topic Re: grouping measures with different conditions to appear in a single column in DAX Commands and Tips

grouping measures with different conditions to appear in a single column

ribs — Tue, 04 Mar 2025 14:13:46 GMT

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

Re: grouping measures with different conditions to appear in a single column

bhanu_gautam — Tue, 04 Mar 2025 15:02:55 GMT

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.

Re: grouping measures with different conditions to appear in a single column

ribs — Tue, 04 Mar 2025 15:15:35 GMT

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

Re: grouping measures with different conditions to appear in a single column

Anonymous — Thu, 24 Apr 2025 08:18:16 GMT

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

Re: grouping measures with different conditions to appear in a single column

Anonymous — Mon, 28 Apr 2025 15:57:22 GMT

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

Re: grouping measures with different conditions to appear in a single column

ribs — Fri, 02 May 2025 06:54:37 GMT

Yes It worked! Thanks!