grouping measures with different conditions to appear in a single column

[ribs]

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

[ribs]

Yes It worked! Thanks!

[v-bmanikante]

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

[v-bmanikante · Accepted 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

[ribs]

@bhanu_gautam 

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

[bhanu_gautam]

@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.