Counting rows with multiple categories per row.

[Dsmiith]

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?

[v-dineshya · Accepted solution]

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"),

    1,

    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

[v-dineshya · Accepted solution]

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"),

    1,

    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

[v-dineshya]

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

[v-dineshya]

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.

[v-dineshya]

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.

[techies]

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

[Dsmiith]

Yes please!
Very much interested!

Do tell!

[techies · Accepted solution]

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

[maruthisp]

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

[Dsmiith]

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!!!

[DataNinja777]

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,