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
(
SELECT DeptID,InsuredLookupID,COUNT(DISTINCT PolicyDesc2) PolicyDesc2
FROM AST.vwDMBoB WHERE DATE='2020-11-05'
GROUP BY DeptID,InsuredLookupID
) A
GROUP BY DeptID

 

Could someone please help me get this solved.

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
      Helper II
      4 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])