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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hammadafridi
Regular Visitor

creating a new table in dax based on 4 new calculated column

Hi i am going to create a new table in which show the two column from the below attached image 

hammadafridi_0-1637836418603.png

 

result:

In result it shows the count of prod_id against those column based on check.

like count of prod_id where bath column is bath and count of prod_id where shower column is shower and count of prod_id where large and small is true. 

 

bath7
shower5
large4
small2
 

 Please help me with it i want to do this through dax

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @hammadafridi 

 

You can try the following methods.

Table2 = 
UNION (
    SUMMARIZE (
        'Table',
        "type", "shower",
        "count", COUNTAX ( FILTER ( 'Table', [shower] = "shower" ), [shower] )
    ),
    SUMMARIZE (
        'Table',
        "type", "bath",
        "count", COUNTAX ( FILTER ( 'Table', [bath] = "bath" ), [bath] )
    ),
    SUMMARIZE (
        'Table',
        "type", "large",
        "count", COUNTAX ( FILTER ( 'Table', [large] = TRUE ), [large] )
    ),
    SUMMARIZE (
        'Table',
        "type", "small",
        "count", COUNTAX ( FILTER ( 'Table', [small] = TRUE ), [small] )
    )
)

vzhangti_0-1638168703730.png

 

Best Regards,

Community Support Team _Charlotte

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-zhangti
Community Support
Community Support

Hi, @hammadafridi 

 

You can try the following methods.

Table2 = 
UNION (
    SUMMARIZE (
        'Table',
        "type", "shower",
        "count", COUNTAX ( FILTER ( 'Table', [shower] = "shower" ), [shower] )
    ),
    SUMMARIZE (
        'Table',
        "type", "bath",
        "count", COUNTAX ( FILTER ( 'Table', [bath] = "bath" ), [bath] )
    ),
    SUMMARIZE (
        'Table',
        "type", "large",
        "count", COUNTAX ( FILTER ( 'Table', [large] = TRUE ), [large] )
    ),
    SUMMARIZE (
        'Table',
        "type", "small",
        "count", COUNTAX ( FILTER ( 'Table', [small] = TRUE ), [small] )
    )
)

vzhangti_0-1638168703730.png

 

Best Regards,

Community Support Team _Charlotte

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

amitchandak
Super User
Super User

@hammadafridi , In power query, Unpivot and group by

https://radacad.com/pivot-and-unpivot-with-power-bi

 

in Dax

Union and summarize

union(

summarize(Table, "Count" , count(Table[Bath]) ,"Column", "Bath" ) ,

summarize(Table, "Count" , count(Table[Shower]) ,"Column", "Shower" ) ,

summarize(Table, "Count" , count(Table[large]) ,"Column", "large" ) ,

summarize(Table, "Count" , count(Table[small]) ,"Column", "small" )

)

 

You can use distinct count if needed

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.