Forum Discussion

Dsmiith's avatar
Dsmiith
Frequent Visitor
1 year ago
Solved

Counting rows with multiple categories per row.

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?

 

"DAX " "DAX help" 'Need help'
new
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    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

  • techies's avatar
    techies
    1 year ago

    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

10 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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,

  • maruthisp's avatar
    maruthisp
    Super User
    1 year ago

    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's avatar
      Dsmiith
      Frequent Visitor
      1 year ago

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

  • techies's avatar
    techies
    Super User
    1 year ago

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

     

     

    • Dsmiith's avatar
      Dsmiith
      Frequent Visitor
      1 year ago

      Yes please!
      Very much interested!

      Do tell!

      • techies's avatar
        techies
        Super User
        1 year ago

        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

  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    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's avatar
      v-dineshya
      Community Support
      1 year ago

      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's avatar
        v-dineshya
        Community Support
        1 year ago

        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.