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], ", " ) )
     
     
     
     
     
LeoQ's avatar
LeoQ
Icon for Helper I rankHelper I
5 years ago

Wow! You are awesome!

Thanks a lot for taking the time to create de pbix file. It wordked perfectly.

What function do you recommend if, instead of showing all the IDs concatenated, I show them in a multiple row card or another table.

Thanks again!

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.