Power Bi Dax create table from other tables and measure

[Karen1025]

Hi I'm pretty new to Power Bi, and am wondering if anyone can help on the issue below? I'm struggled for more than 2 weeks and still couldn't figure out how to do it.  Thanks

 Table "Adviser"

Adviser Key	Authority
1	A
2	B
3	C
4	B
5	A

Table "Practice"

Adviser key

Practice key

Practice group key

Practice group name

How can I use Dax to create a table like below

Practice group name

No. of advisers with Authority "A"

No. of advisers with Authority "B"

The relationship between table "practice" and table "adviser" is Many to One, linked by adviser key

[TomMartens]

Hey @Karen1025, 

I'm not recommending to create a 3rd table, from my understanding of your requirement it will be sufficient to create two measures.

Assuming the relationships between your two tables looks like this:

Then you can create two measures like so:

Advisors with A = 
COUNTROWS(
    FILTER(
        CALCULATETABLE(
            'Advisor'
            , CROSSFILTER( 'Advisor'[Adviser Key] ,Practice[Adviser key] , Both )
        )
        , 'Advisor'[Authority] = "A"
    )
)

and so

Advisors with B = 
COUNTROWS(
    FILTER(
        CALCULATETABLE(
            'Advisor'
            , CROSSFILTER( 'Advisor'[Adviser Key] ,Practice[Adviser key] , Both )
        )
        , 'Advisor'[Authority] = "B"
    )
)

This lets you create a visual using the Table visual like so

I highly recommend reading through this tutorial about data modeling in Power BI: https://docs.microsoft.com/en-us/learn/paths/model-power-bi/?WT.mc_id=DP-MVP-5003068

This article explains how you can create a measure: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-measures?WT.mc_id=DP-MVP-5003068

Hopefully, this helps to tackle your challenge.

Regards,
Tom

P.S.: Enjoy your Power BI journey, from my personal experience i can tell that there is more joy than tears 🙂

[Karen1025]

Hi Tom, I have to create a table for the further calculation, but your measures worked on my report. Thanks anyway. 

[ryan_mayu]

@Karen1025 

could you pls provide the sample data of table practice and the expected output?

[Karen1025]

Hi Ryan,

Please find my tables below

Table"Adviser"

Adviser Key	Authority
A01	A
A02	B
A03	A
A04	B
A05	A
A06	B
A07	A
A08	A

Table "Practice"

Adviser key	Practice key	Practice group key
A01	P001	G1
A02	P002	G1
A03	P003	G2
A04	P004	G2
A05	P001	G1
A06	P002	G1
A07	P003	G2
A08	P004	G2

Expected Output

Practice group name	No. of advisers with Authority "A"	No. of advisers with Authority "B
G1	2	2
G2	3	1

[ryan_mayu · Accepted solution]

@Karen1025 

you can create two measures.

No.ofA = CALCULATE(DISTINCTCOUNT(Adviser[Adviser Key]),FILTER(Adviser,Adviser[Authority]="A"))

No.ofB = CALCULATE(DISTINCTCOUNT(Adviser[Adviser Key]),FILTER(Adviser,Adviser[Authority]="B"))

if you want to create a table, you can try this

Table = SUMMARIZE('Practice',Practice[Practice group key],"NO. of A",CALCULATE(DISTINCTCOUNT(Adviser[Adviser Key]),FILTER(Adviser,Adviser[Authority]="A")),"NO.of B",CALCULATE(DISTINCTCOUNT(Adviser[Adviser Key]),FILTER(Adviser,Adviser[Authority]="B")))

pls see the attachment below.

[Karen1025]

Thanks so much Ryan, this is exactly what I want. You solved my 2 weeks nightmare. 

[ryan_mayu]

you are welcome

[Karen1025]

@amitchandak Thanks for your advise. I'm pretty new to PowerBI, could you please give me an example about how to write the Dax?

[amitchandak]

@Karen1025 , Merge in power query.

Summarize in DAX can work as these are joined tables you can select many table in table and then can use another table.

Or you can also check Natural joins

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/