Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Ouhla
Frequent Visitor

How to count by two categories with DAX ?

Hey everyone, I have my table one, who look like this :

LocationXY

Location_1

AB
Location_1BC
Location_2AC
Location_4CA
.........

 

I've created another table manually to which I'd like to add the count of each note by Location in another column, but I can't manage to code it in DAX to do this, can someone help me?

Expected output :

 

LocationRankCount_XCount_Y

Location_1

A5610
Location_2A3816
Location_3A2363
Location_4A1265
Location_1B7526
Location_2B8337
Location_3B9474
Location_4B3573
Location_1C6415
Location_2C19464
Location_3C28525
Location_4C186241

(All numbers are here for example)


Thanks all for any help !

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

Unpivot your "Table one"  to bring it into usable format.

lbendlin_0-1713640570077.png

 

Then you can use implicit measures to achieve your result, no need for DAX.

 

lbendlin_1-1713640667231.png

 

 

 

View solution in original post

v-nuoc-msft
Community Support
Community Support

Hi @Ouhla 

 

@lbendlin Thank you very much for your prompt reply, and allow me to add something here.

 

As @lbendlin mentioned, you need to do unpivot columns for column "X" and column "Y" and then do the calculations.

 

Here's some dummy data

 

“Table”

vnuocmsft_0-1713856177559.png

 

Select two columns in the power query and click unpivot columns.

 

vnuocmsft_1-1713856220388.png

 

vnuocmsft_2-1713856400744.png

 

vnuocmsft_3-1713856432829.png

 

Create measures.

Count_Y = 
CALCULATE(
    COUNTROWS('Table'), 
    FILTER(
        ALL('Table'), 
        'Table'[Location] = MAX('Table'[Location]) 
        && 
        'Table'[Attribute] = "Y" 
        && 
        'Table'[Rank] = MAX('Table'[Rank])
    )
)

 

Count_X = 
CALCULATE(
    COUNTROWS('Table'), 
    FILTER(
        ALL('Table'), 
        'Table'[Location] = MAX('Table'[Location]) 
        && 
        'Table'[Attribute] = "X" 
        && 
        'Table'[Rank] = MAX('Table'[Rank])
    )
)

 

Here is the result.

 

vnuocmsft_4-1713856619734.png

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

 

 

View solution in original post

2 REPLIES 2
v-nuoc-msft
Community Support
Community Support

Hi @Ouhla 

 

@lbendlin Thank you very much for your prompt reply, and allow me to add something here.

 

As @lbendlin mentioned, you need to do unpivot columns for column "X" and column "Y" and then do the calculations.

 

Here's some dummy data

 

“Table”

vnuocmsft_0-1713856177559.png

 

Select two columns in the power query and click unpivot columns.

 

vnuocmsft_1-1713856220388.png

 

vnuocmsft_2-1713856400744.png

 

vnuocmsft_3-1713856432829.png

 

Create measures.

Count_Y = 
CALCULATE(
    COUNTROWS('Table'), 
    FILTER(
        ALL('Table'), 
        'Table'[Location] = MAX('Table'[Location]) 
        && 
        'Table'[Attribute] = "Y" 
        && 
        'Table'[Rank] = MAX('Table'[Rank])
    )
)

 

Count_X = 
CALCULATE(
    COUNTROWS('Table'), 
    FILTER(
        ALL('Table'), 
        'Table'[Location] = MAX('Table'[Location]) 
        && 
        'Table'[Attribute] = "X" 
        && 
        'Table'[Rank] = MAX('Table'[Rank])
    )
)

 

Here is the result.

 

vnuocmsft_4-1713856619734.png

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

 

 

lbendlin
Super User
Super User

Unpivot your "Table one"  to bring it into usable format.

lbendlin_0-1713640570077.png

 

Then you can use implicit measures to achieve your result, no need for DAX.

 

lbendlin_1-1713640667231.png

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.