Power Bi Dax create table from other tables and measure

[Anonymous]

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

[CerdoLindo]

A follow up question here:   in PowerBI I have a (complex) DAX query that works great within the DAX query view, and returns a table . 

Now I want to use the (complex) query definition to define a table.... yet it fails. Is there any smart way? 

Hello Community!  I Am trying to use a DAX query to create a table...   My query works fine in the DAX editor... but I fail to transpose it as a table DAX-definition.  

DAX output is:   

SERIES	ACTIVITY	TCO	SERIES_VALUE
SpecCost	Clean Energy	48.5	0
SpecCost	Increase Recycling	683	0
SpecCost	Convert PDD	84.5	1.77

I would like to have this as a table....   Any idea how to proceed ?   🙂 Thanks & KR, Alfred

DAX query view code:

// DAX Query
DEFINE
COLUMN '__SQDS0VisualCalcs'[Percent of grand total] =   ( BLANK() )
COLUMN '__SQDS0VisualCalcs'[Percent of grand total 1] = ( BLANK() )
VAR __SQDS0FilterTable =  TREATAS({"2030"}, 'Q6_i6b_ABAT_sort'[YEAR_STREAM])
VAR __SQDS0FilterTable2 =  TREATAS({"Ghirardelli Chocolate Company"}, 'Q6_i6b_ABAT_sort'[SITE])

VAR __SQDS0FilterTable3 = 

FILTER(
KEEPFILTERS(VALUES('Q6_i6b_ABAT_sort'[SCOPE_SBT_GROUP_FLAG_TYPE])),
NOT('Q6_i6b_ABAT_sort'[SCOPE_SBT_GROUP_FLAG_TYPE] IN {"Scope ALL"})
)

VAR __ValueFilterDM2 = 
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Q6_i6b_ABAT_sort'[Index],
'Q6_i6b_ABAT_sort'[Action name],
__SQDS0FilterTable,
__SQDS0FilterTable2,
__SQDS0FilterTable3,
"SumSpecCost_kCHF_tCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[SpecCost_kCHF_tCO2e])),
"SumtCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[tCO2e])),
"meas_MAX", 'Q6_i6b_ABAT_sort'[meas_MAX],
"meas_COMPL", 'Q6_i6b_ABAT_sort'[meas_COMPL]
)
),
[SumSpecCost_kCHF_tCO2e] >= 0
)

VAR __SQDS0Core = 
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'Q6_i6b_ABAT_sort'[Index], "IsSQDS0GrandTotalRowTotal",
'Q6_i6b_ABAT_sort'[Action name], "IsDM0Total"
),
__SQDS0FilterTable,
__SQDS0FilterTable2,
__SQDS0FilterTable3,
__ValueFilterDM2,
"SumSpecCost_kCHF_tCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[SpecCost_kCHF_tCO2e])),
"SumtCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[tCO2e])),
"meas_MAX", 'Q6_i6b_ABAT_sort'[meas_MAX],
"meas_COMPL", 'Q6_i6b_ABAT_sort'[meas_COMPL]
)



VAR __SQDS0VisualCalcsInput = 
SELECTCOLUMNS(
KEEPFILTERS(
SELECTCOLUMNS(
__SQDS0Core,
"Index", 'Q6_i6b_ABAT_sort'[Index],
"IsSQDS0GrandTotalRowTotal", [IsSQDS0GrandTotalRowTotal],
"Action_name", 'Q6_i6b_ABAT_sort'[Action name],
"IsDM0Total", [IsDM0Total],
"SumSpecCost_kCHF_tCO2e", [SumSpecCost_kCHF_tCO2e],
"SumtCO2e", [SumtCO2e],
"meas_MAX", [meas_MAX],
"meas_COMPL", [meas_COMPL]
)
),

"Sum of Index", [Index],
"Action name", [Action_name],
"IsSQDS0GrandTotalRowTotal", [IsSQDS0GrandTotalRowTotal],
"IsDM0Total", [IsDM0Total],
"SpecCost_kCHF_tCO2e", [SumSpecCost_kCHF_tCO2e],
"tCO2e", [SumtCO2e],
"meas_MAX", [meas_MAX],
"meas_COMPL", [meas_COMPL]
)

TABLE '__SQDS0VisualCalcs' = 
__SQDS0VisualCalcsInput
WITH VISUAL SHAPE
AXIS rows
GROUP [Sum of Index] TOTAL [IsSQDS0GrandTotalRowTotal]
GROUP [Action name] TOTAL [IsDM0Total]   
ORDER BY   [Sum of Index] ASC,   [Action name] ASC   DENSIFY "IsDensifiedRow"

VAR __SQDS0RemoveEmptyDensified = 
FILTER(
KEEPFILTERS('__SQDS0VisualCalcs'),
OR(  NOT('__SQDS0VisualCalcs'[IsDensifiedRow]),    NOT(ISBLANK('__SQDS0VisualCalcs'[Percent of grand total]))    )
)

VAR __DS0Core = 
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(__SQDS0RemoveEmptyDensified),
AND(
'__SQDS0VisualCalcs'[IsSQDS0GrandTotalRowTotal] = FALSE,
'__SQDS0VisualCalcs'[IsDM0Total] = FALSE
)
)
),
"'__SQDS0VisualCalcs'[Sum of Index]", '__SQDS0VisualCalcs'[Sum of Index],
"'__SQDS0VisualCalcs'[Action name]", '__SQDS0VisualCalcs'[Action name],
"'__SQDS0VisualCalcs'[SpecCost_kCHF_tCO2e]", '__SQDS0VisualCalcs'[SpecCost_kCHF_tCO2e],
"'__SQDS0VisualCalcs'[tCO2e]", '__SQDS0VisualCalcs'[tCO2e],
"'__SQDS0VisualCalcs'[Percent of grand total]", '__SQDS0VisualCalcs'[Percent of grand total],
"'__SQDS0VisualCalcs'[Percent of grand total 1]", '__SQDS0VisualCalcs'[Percent of grand total 1],
"'__SQDS0VisualCalcs'[meas_MAX]", '__SQDS0VisualCalcs'[meas_MAX],
"'__SQDS0VisualCalcs'[meas_COMPL]", '__SQDS0VisualCalcs'[meas_COMPL]
)

VAR __DS0RemoveContextOnlyColumns = UNION(
SELECTCOLUMNS(
KEEPFILTERS(__DS0Core),
"Series","SpecCost",
"Action name", '__SQDS0VisualCalcs'[Action name],
"tCO2e", '__SQDS0VisualCalcs'[tCO2e],
"SeriesVALUE", '__SQDS0VisualCalcs'[SpecCost_kCHF_tCO2e]
), 
SELECTCOLUMNS(
KEEPFILTERS(__DS0Core),
"Series","SpecCost_COMPL",
"Action name", '__SQDS0VisualCalcs'[Action name],
"tCO2e", '__SQDS0VisualCalcs'[tCO2e],
"SeriesVALUE", '__SQDS0VisualCalcs'[meas_COMPL]
)
)


VAR OUTPUT = TOPN(501, __DS0RemoveContextOnlyColumns, 1 )

EVALUATE
OUTPUT

[TomMartens]

Hey @Anonymous, 

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 🙂

[Anonymous]

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

[ryan_mayu]

@Anonymous 

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

[Anonymous]

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]

@Anonymous 

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.

[Anonymous]

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

[ryan_mayu]

you are welcome

[Anonymous]

@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]

@Anonymous , 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/