Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a set of measures which basically calculates the count of orderid but the conditions are different which gives actuals. So I have created three different measures which form three different columns. Instead I need all of them to show up in one column, I am new to DAX and need your help here. Thank you in advance
My Table:
Sending Type | Total Order Count | Express | Express 10:00 | Express 12:00 |
Express | 100 | 10 | ||
Express 10:00 | 20 | 20 | ||
Express 12:00 | 30 | 30 | ||
Express = CALCULATE(count(Query1[Orderno]); filter(Query1; format(Query1[DEL];"hh:mm") <= "18:00") ; FILTER(Query1;Query1[OUB]<= 24); FILTER(Query1;Query1[Sending]= "EXPRESS"))
Express 10 = CALCULATE(count(Query1[Orderno]); filter(Query1; format(Query1[DEL];"hh:mm") <= "10:00") ; FILTER(Query1;Query1[OUB]<= 24); FILTER(Query1;Query1[Sending]= "EXPRESS 10"))
Expected Output:
Sending Type | Total Order Count | Express |
Express | 100 | 100 |
Express 10:00 | 20 | 20 |
Express 12:00 | 30 | 30 |
Solved! Go to Solution.
Hi @ribs ,
Thank you for reaching out to Microsoft Fabric Community Forum.
@bhanu_gautam Thank you for your response.
To display all your different order count measures (like Express, Express 10:00, and Express 12:00) in a single column, you can use a disconnected table and a SWITCH measure.
Create a calculated table for your sending types:
SendingTypes = DATATABLE(
"Sending Type", STRING,
{
{"Express"},
{"Express 10:00"},
{"Express 12:00"}
}
)
Then, Create a dynamic measure using SWITCH:
Dynamic OrderCount =
SWITCH(
SELECTEDVALUE(SendingTypes[Sending Type]),
"Express", CALCULATE(COUNT(Query1[Orderno]), FILTER(Query1, FORMAT(Query1[DEL], "hh:mm") <= "18:00" && Query1[OUB] <= 24 && Query1[Sending] = "EXPRESS")),
"Express 10:00", CALCULATE(COUNT(Query1[Orderno]), FILTER(Query1, FORMAT(Query1[DEL], "hh:mm") <= "10:00" && Query1[OUB] <= 24 && Query1[Sending] = "EXPRESS 10")),
"Express 12:00", CALCULATE(COUNT(Query1[Orderno]), FILTER(Query1, FORMAT(Query1[DEL], "hh:mm") <= "12:00" && Query1[OUB] <= 24 && Query1[Sending] = "EXPRESS 12"))
)
Use a matrix or table visual in Power BI with:
SendingTypes[Sending Type] as rows
Dynamic OrderCount as values
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Yes It worked! Thanks!
Hi @ribs ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara Reddy
Hi @ribs ,
Thank you for reaching out to Microsoft Fabric Community Forum.
@bhanu_gautam Thank you for your response.
To display all your different order count measures (like Express, Express 10:00, and Express 12:00) in a single column, you can use a disconnected table and a SWITCH measure.
Create a calculated table for your sending types:
SendingTypes = DATATABLE(
"Sending Type", STRING,
{
{"Express"},
{"Express 10:00"},
{"Express 12:00"}
}
)
Then, Create a dynamic measure using SWITCH:
Dynamic OrderCount =
SWITCH(
SELECTEDVALUE(SendingTypes[Sending Type]),
"Express", CALCULATE(COUNT(Query1[Orderno]), FILTER(Query1, FORMAT(Query1[DEL], "hh:mm") <= "18:00" && Query1[OUB] <= 24 && Query1[Sending] = "EXPRESS")),
"Express 10:00", CALCULATE(COUNT(Query1[Orderno]), FILTER(Query1, FORMAT(Query1[DEL], "hh:mm") <= "10:00" && Query1[OUB] <= 24 && Query1[Sending] = "EXPRESS 10")),
"Express 12:00", CALCULATE(COUNT(Query1[Orderno]), FILTER(Query1, FORMAT(Query1[DEL], "hh:mm") <= "12:00" && Query1[OUB] <= 24 && Query1[Sending] = "EXPRESS 12"))
)
Use a matrix or table visual in Power BI with:
SendingTypes[Sending Type] as rows
Dynamic OrderCount as values
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
After this statement
Combined Express =
SWITCH(
TRUE(),
it is not allowing to choose the columns from the table except for measures. The auto suggested columns show only measures
@ribs Create a new calculated column that will hold the combined measure values based on the conditions.
DAX
Combined Express =
SWITCH(
TRUE(),
Query1[Sending Type] = "Express", CALCULATE(COUNT(Query1[Orderno]), FILTER(Query1, FORMAT(Query1[DEL], "hh:mm") <= "18:00") && Query1[OUB] <= 24 && Query1[Sending] = "EXPRESS"),
Query1[Sending Type] = "Express 10:00", CALCULATE(COUNT(Query1[Orderno]), FILTER(Query1, FORMAT(Query1[DEL], "hh:mm") <= "10:00") && Query1[OUB] <= 24 && Query1[Sending] = "EXPRESS 10"),
Query1[Sending Type] = "Express 12:00", CALCULATE(COUNT(Query1[Orderno]), FILTER(Query1, FORMAT(Query1[DEL], "hh:mm") <= "12:00") && Query1[OUB] <= 24 && Query1[Sending] = "EXPRESS 12"),
BLANK()
)
Use this new column in your table to display the combined measure values in a single column.
Proud to be a Super User! |
|
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
7 |