DAX query

swetas

I am working on powerbi report builder for making a report. I am stuck at a query.

Please help me out in writing a query for this.
Add a new column called 'grantTypeDesc' that will have the descriptive version of grantee[granteeTypeCode].

marcothesane

Add a new column - to what? An already existing query? If yes, can you share it?

swetas

Yes, add a new column to an existing query.

marcothesane

We need more info to help you:

• what do you mean by a "DAX query"?
• What is the table / are the tables that your existing query is selecting from ?
• "grantee" us the user or role that is granted SELECT, INSERT, UPDATE, DELETE on a view or table. Which is the view or table that you want the grantee of ?

The place where you find privileges info in the Vertica system catalogue is the grants system table:

CREATE TABLE grants (
  grant_id               BIGINT       
, grantor_id             BIGINT       
, grantor                VARCHAR(128) 
, privileges_description VARCHAR(8192)
, object_schema          VARCHAR(128) 
, object_name            VARCHAR(257) 
, object_id              BIGINT       
, object_type            VARCHAR(8192)
, grantee_id             BIGINT       
, grantee                VARCHAR(128) 
)
;

This is a sample entry:

-[ RECORD 1 ]----------+------------------
grant_id               | 45035996273705286
grantor_id             | 45035996273704962
grantor                | dbadmin
privileges_description | EXECUTE*
object_schema          | public
object_name            | Explode
object_id              | 45035996276941580
object_type            | PROCEDURE
grantee_id             | 45035996273704962
grantee                | dbadmin

Share the existing query with us, and what kind of info you expect in the grantee type code or its description.

swetas

Here is a query -
DEFINE
VAR vFromgrantsgrantdate1 =
DATEVALUE ( @Fromgrantsgrantdate )
VAR vTograntsgrantdate1 =
DATEVALUE ( @Tograntsgrantdate )
MEASURE grants[GrantTotalsByIdCode] =
CALCULATE (
SUM ( grants[grantamt] ),
ALLSELECTED ( grants ),
VALUES ( grants[idcode] )
)
MEASURE grants[GrantYear] =
YEAR ( SELECTEDVALUE ( grants[grantdate] ) )
MEASURE grants[GrantMonth] =
SWITCH (
MONTH ( SELECTEDVALUE ( grants[grantdate] ) ),
1, "January",
2, "February",
3, "March",
4, "April",
5, "May",
6, "June",
7, "July",
8, "August",
9, "September",
10, "October",
11, "November",
12, "December"
)

EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
grants[grantnum],
grants[grantdate],
grants[idcode],
profile[orgname],
grants[grantamt],
grants[fundid],
grants[action],
grantee[datefirstgrant],
grantee[granteetypecode],
grantee[popcode],
"grantTotalsByIdCode", [GrantTotalsByIdCode],
"grantYear", [GrantYear],
"grantMonth", [GrantMonth]
),
( grants[grantdate] >= DATEVALUE ( vFromgrantsgrantdate1 ) ),
( grants[grantdate] <= DATEVALUE ( vTograntsgrantdate1 ) ),
( grantee[granteetypecode] <> "T" ),
( grants[grantamt] <> 0 )
)
ORDER BY
"grantTotalsByIdCode" DESC,
grants[grantamt],
profile[orgname] DESC