Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create single measure/table for two level calculation

I am trying to create a DAX measure/table which can perform the below complex SQL operation in Power BI Desktop.   SELECT DeptID,COUNT(DISTINCT InsuredLookupID) Accounts,SUM(PolicyDesc2) Lines FROM...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , You can have two meausres like

    measure PolicyDesc2 =
    sumx(summarize(filter(vwDMBoB, vwDMBoB[DATE]= date(2020,1,05)),vwDMBoB[DeptID],vwDMBoB[InsuredLookupID], "PolicyDesc2",distinctcount(vwDMBoB[PolicyDesc2])),[PolicyDesc2])

     

    measure Accounts =
    countx(summarize(filter(vwDMBoB, vwDMBoB[DATE]= date(2020,1,05)),vwDMBoB[DeptID],vwDMBoB[InsuredLookupID], "PolicyDesc2",distinctcount(vwDMBoB[PolicyDesc2])),[InsuredLookupID])

     

    if filter of 2015 is static , you can create a table

    summarize(filter(vwDMBoB, vwDMBoB[DATE]= date(2020,1,05)),vwDMBoB[DeptID],vwDMBoB[InsuredLookupID], "PolicyDesc2",distinctcount(vwDMBoB[PolicyDesc2]))

     

    and use that

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , You can have two meausres like

measure PolicyDesc2 =
sumx(summarize(filter(vwDMBoB, vwDMBoB[DATE]= date(2020,1,05)),vwDMBoB[DeptID],vwDMBoB[InsuredLookupID], "PolicyDesc2",distinctcount(vwDMBoB[PolicyDesc2])),[PolicyDesc2])

 

measure Accounts =
countx(summarize(filter(vwDMBoB, vwDMBoB[DATE]= date(2020,1,05)),vwDMBoB[DeptID],vwDMBoB[InsuredLookupID], "PolicyDesc2",distinctcount(vwDMBoB[PolicyDesc2])),[InsuredLookupID])

 

if filter of 2015 is static , you can create a table

summarize(filter(vwDMBoB, vwDMBoB[DATE]= date(2020,1,05)),vwDMBoB[DeptID],vwDMBoB[InsuredLookupID], "PolicyDesc2",distinctcount(vwDMBoB[PolicyDesc2]))

 

and use that

fab196's avatar
fab196
Icon for Helper II rankHelper II
5 years ago

amitchandak 

hi sir ,

 

kind of similar requiremnt mine alos ...i tried with same logic but it is not working 

 

error :

The expression specified in the query is not a valid table expression.

 

DAX EXPRESSION I HAVE TRIED:

 

table data = SUMX(summarize(Table,Table[LOCATION],Table[MODULE], "PolicyDesc2",COUNT(id)),[PolicyDesc2])