topic Re: Counting rows with multiple categories per row. in DAX Commands and Tips

Counting rows with multiple categories per row.

Dsmiith — Mon, 26 May 2025 10:39:07 GMT

Hi!

I have searched the forum and can't find the solution, so i am doing my first post here.

I have a data set with Identifications number in column A, and parameters in column B.
I need to figure out per identification a combination of categorical data is used. Se picture.

So what I would need is a new column which indicates
If the identification number has a combination of parameters.

So if the parameters are equal to "PTI" or "Totalbiomassa" the column give me a 1, otherwise a blank.

Is it understandable?

Re: Counting rows with multiple categories per row.

DataNinja777 — Mon, 26 May 2025 14:31:54 GMT

Hi Dsmiith ,

To mark each row with a 1 if the corresponding Vattenförekomstens id has both the parameters "PTI" and "Totalbiomassa", you can use a calculated column in Power BI with the following DAX formula:

HasPTIandBiomassa = VAR CurrentID = 'YourTableName'[Vattenförekomstens id] VAR HasPTI = CALCULATE ( COUNTROWS ( 'YourTableName' ), 'YourTableName'[Vattenförekomstens id] = CurrentID, 'YourTableName'[Parameter] = "PTI" ) > 0 VAR HasBiomassa = CALCULATE ( COUNTROWS ( 'YourTableName' ), 'YourTableName'[Vattenförekomstens id] = CurrentID, 'YourTableName'[Parameter] = "Totalbiomassa" ) > 0 RETURN IF ( HasPTI && HasBiomassa, 1 )

This formula checks each Vattenförekomstens id to see if both "PTI" and "Totalbiomassa" exist in the rows associated with that ID. If both are present, it returns 1 for all rows with that ID; otherwise, it returns blank. Replace 'YourTableName' with the actual name of your table in Power BI.

Best regards,

Re: Counting rows with multiple categories per row.

maruthisp — Tue, 27 May 2025 01:25:18 GMT

Hi Dsmiith,

You want to flag each row if that Identification number (ID) has both “PTI” and “Totalbiomassa” among its parameters (not just the current row’s value, but if both exist somewhere for that ID).

ComboFlag =
VAR ThisID = YourTable[ID]
VAR HasPTI =
CALCULATE(
COUNTROWS(YourTable),
YourTable[ID] = ThisID,
YourTable[Parameter] = "PTI"
) > 0
VAR HasTotalbiomassa =
CALCULATE(
COUNTROWS(YourTable),
YourTable[ID] = ThisID,
YourTable[Parameter] = "Totalbiomassa"
) > 0
RETURN
IF(HasPTI && HasTotalbiomassa, 1, BLANK())

This checks, for every row, if both “PTI” and “Totalbiomassa” exist for the same ID. If yes, it flags all those rows with 1, else leaves it blank.

please check it and let me know if you have any questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!

Best Regards,

Maruthi

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/

X - Maruthi Siva Prasad - (@MaruthiSP) / X

Re: Counting rows with multiple categories per row.

Dsmiith — Tue, 27 May 2025 07:03:36 GMT

Unfortunately it did not really work, believe it is due to that the values in column parameters will always only have one value. So it can only be wither PTI or Totalbiomassa.

But it could still have the same ID.

So the formula does not match in any row, all is empty. But if I instead of && do ||. I get the correct results for the or.

Could you instead do a sumx?

So the result would be:
1 if either PTI or Totalbiomassa per ID
2 if both PTI and totalbiomassa per ID
0 if neither PTI and totalbiomassa per ID

Many thanks for the response!!!

Re: Counting rows with multiple categories per row.

techies — Tue, 27 May 2025 07:20:25 GMT

Hi Dsmiith in case you prefer power query editor, there is a solution from there, let me know

Re: Counting rows with multiple categories per row.

v-dineshya — Tue, 27 May 2025 08:46:47 GMT

Hi Dsmiith ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Please check below calculated column "PTI_Totalbiomassa_Flag" to represent the flag values, if the parameters are equal to "PTI" or "Totalbiomassa" the column gives 1, otherwise a blank.

1. Created sample data , please refer snap.

2. Created Calculated column with below DAX .

PTI_Totalbiomassa_Flag =

VAR CurrentID = 'Table'[Vattenförekomstens id]

VAR CurrentParameter = 'Table'[Parameter]

VAR HasPTI =

CALCULATE(

COUNTROWS('Table'),

ALLEXCEPT('Table', 'Table'[Vattenförekomstens id]),

'Table'[Parameter] = "PTI"

) > 0

VAR HasTotalbiomassa =

CALCULATE(

COUNTROWS('Table'),

ALLEXCEPT('Table', 'Table'[Vattenförekomstens id]),

'Table'[Parameter] = "Totalbiomassa"

) > 0

RETURN

IF(

HasPTI && HasTotalbiomassa &&

(CurrentParameter = "PTI" || CurrentParameter = "Totalbiomassa"),

BLANK()

)

3. please refer output snaps and attached PBIX file.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

Re: Counting rows with multiple categories per row.

Dsmiith — Tue, 27 May 2025 11:09:40 GMT

Yes please!
Very much interested!

Do tell!

Re: Counting rows with multiple categories per row.

techies — Tue, 27 May 2025 16:28:55 GMT

Hi, first group your data using ALLROWS

then add custom column

let
paramList = List.Transform([Allrows][Parameter], each _)
in
if List.Contains(paramList, "PTI") and List.Contains(paramList, "Totalbiomassa") then 1 else null

and then expand allrows column

Re: Counting rows with multiple categories per row.

v-dineshya — Fri, 30 May 2025 10:36:15 GMT

Hi Dsmiith ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

Re: Counting rows with multiple categories per row.

v-dineshya — Mon, 02 Jun 2025 17:17:12 GMT

Hi Dsmiith ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

Re: Counting rows with multiple categories per row.

v-dineshya — Thu, 05 Jun 2025 18:27:14 GMT

Hi @Dsmiith ,

Thank you.