Forum Discussion

DominicBrien's avatar
DominicBrien
Frequent Visitor
9 years ago

Count related items

Hello, I have a quite basic scenario (in Direct query) but I cannot find a basic solution for it.   It only involves 2 tables and a relation between the two.   Lets say I have a Person table and ...
Need Help
cs_skit's avatar
cs_skit
Resolver IV
9 years ago

To solve it I had to create a new table with this DAX:

then you can do this:

Didn't get it to work quickly without the extra table I don't use SUMMARIZE often.

Maybe you can figure out if you can do it without the extra table but this works.

DominicBrien's avatar
DominicBrien
Frequent Visitor
9 years ago

Hello, were you able to to this in direct query mode? My understanding was that table creation was not enabled in direct query mode...

What you did does a simmilar result to what I get with group by but I would also have an issue when attempting to filter on the date of cases.

 

Regards,

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi DominicBrien,



    Hello, were you able to to this in direct query mode? My understanding was that table creation was not enabled in direct query mode...

    What you did does a simmilar result to what I get with group by but I would also have an issue when attempting to filter on the date of cases.

    I don't think there is an easy way to do it with Power BI currently. You may need to add a new table which contains a column of all possible values of "Count of cases"(i.e. 1~1000 or more) to your source db, and add it to your Power BI Data Mode like below first.

     

     

    Then you should be able to use the formula below to create a measure to calculate the count of CaseSubject for the corresponding "Count of cases". And show the measure with the "Count of cases" column from the new added table on the report.  In this way, the result can be filtered by other columns(date field or CaseId field).

    Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Table1,
            Table1[CaseSubject],
            "CountOfCaseID", COUNT ( Table1[CaseID] ),
            "CSub", Table1[CaseSubject]
        ),
        [CountOfCaseID] = MAX ( Table2[Count Of CaseID] )
    )
)

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards