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

DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper 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,