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

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