Forum Discussion

LeoQ's avatar
LeoQ
Icon for Helper I rankHelper I
5 years ago
Solved

Detect when a distinct count exceeds a number.

Hi everybody, I spent lot of hours looking for a solution but I found nothing. I have this table, that's made by a form completition. Each request might be made by different departments and might be...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

    Requests Count made by three dept or more : =
    VAR _newtable =
    ADDCOLUMNS (
    SUMMARIZE ( Data, Requests[Request ID] ),
    "@deptcount", CALCULATE ( COUNTROWS ( DISTINCT ( Data[Department] ) ) )
    )
    VAR _filtertable =
    FILTER ( _newtable, [@deptcount] >= 3 )
    VAR _summarizefilteredtable =
    SUMMARIZE ( _filtertable, Requests[Request ID] )
    RETURN
    IF ( HASONEVALUE ( Agents[Agent] ), COUNTROWS ( _summarizefilteredtable ) )
     
    Requests List made by three dept or more : =
    VAR _newtable =
    ADDCOLUMNS (
    SUMMARIZE ( Data, Requests[Request ID] ),
    "@deptcount", CALCULATE ( COUNTROWS ( DISTINCT ( Data[Department] ) ) )
    )
    VAR _filtertable =
    FILTER ( _newtable, [@deptcount] >= 3 )
    VAR _summarizefilteredtable =
    SUMMARIZE ( _filtertable, Requests[Request ID] )
    RETURN
    IF ( HASONEVALUE ( Agents[Agent] ), CONCATENATEX( _summarizefilteredtable, Requests[Request ID], ", " ) )
     
     
     
     
     
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi,

Thank you for your feedback.

I am not sure how you want to see your final outcome, but please check the below picture and the link to the sample pbix file.

 

 

 

Link to the sample pbix file 

 

 

LeoQ's avatar
LeoQ
Icon for Helper I rankHelper I
5 years ago

It worked perfectly! Thanks a lot for your  time.