Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Based on the Value column to calculate the count of rows

Ob_ID          lastUpdated              code                   ClientID       value
1                  8/14/2020 12:22       B1                       A-100            95
1                 8/14/2020 12:22        B2                       A-100             78
2                8/14/2020 12:24         B1                       A-100              90
2               8/14/2020 12:24         B2                       A-100               75
3              8/14/2020 12:28          B1                       A-101              105
3              8/14/2020 12:28          B2                       A-101               66
4              8/14/2020 12:20          B1                       A-102                98
4               8/14/2020 12:20        B2                        A-102                 55
5              8/14/2020 12:22         B1                       A-102                103
5              8/14/2020 12:22         B2                       A-102                75
6             8/14/2020 12:22         B1                       A-103                112
6            8/14/2020 12:22         B2                        A-103                   75
7            8/14/2020 12:22         B1                       A-103                  115
7            8/14/2020 12:22        B2                        A-103                    78


Requirment:

code                    Highfrequency                                                       Mediumfrequency                      Normal

B1                       value less than 100 or greater than 150                 100-109 or 131-150                 110-130
B2                      value less than 60 or greater than 100                    60-69 or 91-100                        70-90


need to create dax for Highfrequency
if ((code=B1 and (value<100 or value>150))
or (code=B2 and (value<60 or value>100) then 'Highfrequency' )


Mediumfrequency
if ((code=B1 and ((value>100 and value<109) or (value>131 and value<150))
or ((code=B2 and ((value>60 and value<69) or (value>91 and value<100))
then 'Mediumfrequency' )


Output:-

Ob_ID          lastUpdated              code                   ClientID         value    Status
1                  8/14/2020 12:22       B1                       A-100            95         HighFrequency
1                 8/14/2020 12:22        B2                       A-100             78        Normal
2                8/14/2020 12:24         B1                       A-100              90      HighFrequency
2               8/14/2020 12:24         B2                       A-100               75      normal
3              8/14/2020 12:28          B1                       A-101              105     Mediumfrequency
3              8/14/2020 12:28          B2                       A-101               66      Mediumfrequency
4              8/14/2020 12:20          B1                       A-102                98     HighFrequency
4               8/14/2020 12:20        B2                        A-102                 55    HighFrequency
5              8/14/2020 12:22         B1                       A-102                103    Mediumfrequency
5              8/14/2020 12:22         B2                       A-102                75       Mediumfrequency  
6             8/14/2020 12:22         B1                       A-103                112       Normal
6            8/14/2020 12:22         B2                        A-103                   75      Normal
7            8/14/2020 12:22         B1                       A-103                  115       Normal
7            8/14/2020 12:22        B2                        A-103                    78        Normal  

 

In report KPI should be look like:-

 

HIGH+MediumFrequency           HighFrequency              Mediumfrequency      
7                                                       4                                      3                            

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Create a new column

Switch(True()
([code]="B1" && ([value]<100 || [value]>150))
|| ([code]="B2" && ([value]<60 || [value]>100)) , "Highfrequency",
([code]="B1" && ([value]>100 && [value]<109) || ([value]>131 && [value]<150))
|| (([code]=B2 && ([value]>60 && [value]<69) || ([value]>91 && [value]<100))) , "Mediumfrequency" ,
"Normal")
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@Anonymous 

I think something's wrong with your expected output.

Ob_ID lastUpdated code ClientID value Status
1 8/14/2020 12:22 B1 A-100 95 HighFrequency
1 8/14/2020 12:22 B2 A-100 78 Normal
2 8/14/2020 12:24 B1 A-100 90 HighFrequency
2 8/14/2020 12:24 B2 A-100 75 normal
3 8/14/2020 12:28 B1 A-101 105 Mediumfrequency
3 8/14/2020 12:28 B2 A-101 66 Mediumfrequency
4 8/14/2020 12:20 B1 A-102 98 HighFrequency
4 8/14/2020 12:20 B2 A-102 55 HighFrequency
5 8/14/2020 12:22 B1 A-102 103 Mediumfrequency
5 8/14/2020 12:22 B2 A-102 75 Mediumfrequency
6 8/14/2020 12:22 B1 A-103 112 Normal
6 8/14/2020 12:22 B2 A-103 75 Normal
7 8/14/2020 12:22 B1 A-103 115 Normal
7 8/14/2020 12:22 B2 A-103 78 Normal

 

you can try this

status = if((Tabelle1[code]="B1"&&(Tabelle1[value]<100 || Tabelle1[value]>150))||(Tabelle1[code]="B2"&&(Tabelle1[value]<60||Tabelle1[value]>100)),"Highfrequency",if((Tabelle1[code]="B1"&&((Tabelle1[value]>100 && Tabelle1[value]<109)||(Tabelle1[value]>131 &&Tabelle1[value]<150)))||(Tabelle1[code]="B2"&&((Tabelle1[value]>60 &&Tabelle1[value]<69)||(Tabelle1[value]>91&&Tabelle1[value]<100))),"Mediumfrequency","normal"))

1.PNG

then you can create a visual with unselecting normal

3.PNG2.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@Anonymous , Create a new column

Switch(True()
([code]="B1" && ([value]<100 || [value]>150))
|| ([code]="B2" && ([value]<60 || [value]>100)) , "Highfrequency",
([code]="B1" && ([value]>100 && [value]<109) || ([value]>131 && [value]<150))
|| (([code]=B2 && ([value]>60 && [value]<69) || ([value]>91 && [value]<100))) , "Mediumfrequency" ,
"Normal")
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

Thank you @amitchandak 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.