Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
What I would like to get is column/measure where I could get following:
Status count per Code ( unique) but if Status = Sold, it will count values from column Processing.
Value: Ordered - count 1
Value: In Delivery - count 4
from Status column
+
Count unique values in column Processing per Group
Value: Jeans - count 1
Value: Shirt - count 3
from column Processing
Code | Group | Processing | Calculated column distinc count of code per group | Status |
101 | Jeans | 1 | 2 | Ordered |
101 | Jeans | 1 | 2 | Ordered |
102 | Jeans | 1 | 2 | In Delivery |
103 | Shirt | 3 | 4 | In Delivery |
104 | Shirt | 3 | 4 | In Delivery |
105 | Shirt | 3 | 4 | In Delivery |
106 | Shirt | 3 | 4 | Sold |
Solved! Go to Solution.
Hi , @MasterSonic
According to your description, you want to add a "Processing" in the table . Right?
Here are the steps you can refer to :
(1)We need to create a table like this:
(2)Then we need to check we can not create relationship between two tables:
(3)Then we create a measure like this:
Value = var _status = SELECTEDVALUE('Status Table'[Status])
var _processing =DISTINCT( SELECTCOLUMNS( ALLSELECTED('Table') ,"Processing" , [Processing]))
var _t=DISTINCT(SELECTCOLUMNS( FILTER( ALLSELECTED('Table') ,'Table'[Status]=_status) , "Code" , [Code]))
var _pro_value =SUMX( SUMMARIZE(ALLSELECTED('Table'),[Group],[Processing]) , [Processing])
return
IF(_status="Processing",_pro_value,IF(_status ="Sold" , COUNTROWS(_processing) , COUNTROWS(_t)))
(4)Then we put this field we need on the pir visual and we will meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , @MasterSonic
Sorry , i do not fully understand your need. In my side , i calculate the "IF status =Sold , return distinctcount of the [Processing] value in the table . else return the distinctcount of the [Code] group by the [Status] in the table".
Here are the steps in my test:
(1)This is my test data:
(2)We can create a measure:
Status count per Code = var _status = SELECTEDVALUE('Table'[Status])
var _processing =DISTINCT( SELECTCOLUMNS( ALLSELECTED('Table') ,"Processing" , [Processing]))
var _t=DISTINCT(SELECTCOLUMNS( FILTER( ALLSELECTED('Table') ,'Table'[Status]=_status) , "Code" , [Code]))
return
IF(_status ="Sold" , COUNTROWS(_processing) , COUNTROWS(_t))
(3)Then we put the fields we need on the visual and we will get this result :
For your need , if this method donot meet you need , can you give the end result you want as a table to us according to your sample data ?
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Aniya,
You helped a lot with that.
You solution basically solve the problem.
Only things is how to create seperate value for that?
I will quote you here:
"IF status =Sold , return distinctcount of the [Processing] value in the table . else return the distinctcount of the [Code] group by the [Status] in the table".
How to add new value let's call it "Processing"
alongside
"Ordered", "In delivery","Sold"
Hi, @MasterSonic
Sorry , i am not very fully understanding about the " add new value let's call it "Processing"".
Can you share the end result you want to need as a table according to your test data to us so that we can help you better.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
hello, i would like to creata a pie chart like that
Hi , @MasterSonic
According to your description, you want to add a "Processing" in the table . Right?
Here are the steps you can refer to :
(1)We need to create a table like this:
(2)Then we need to check we can not create relationship between two tables:
(3)Then we create a measure like this:
Value = var _status = SELECTEDVALUE('Status Table'[Status])
var _processing =DISTINCT( SELECTCOLUMNS( ALLSELECTED('Table') ,"Processing" , [Processing]))
var _t=DISTINCT(SELECTCOLUMNS( FILTER( ALLSELECTED('Table') ,'Table'[Status]=_status) , "Code" , [Code]))
var _pro_value =SUMX( SUMMARIZE(ALLSELECTED('Table'),[Group],[Processing]) , [Processing])
return
IF(_status="Processing",_pro_value,IF(_status ="Sold" , COUNTROWS(_processing) , COUNTROWS(_t)))
(4)Then we put this field we need on the pir visual and we will meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |