cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Fetch column names from respective dimension tables where rev>10 and rev%>0.06

HI All,

I have 2 dimension tables, these 2 tables do not have any relationship between them. There is no common column for performing join.

1.DimMU table with "mu " column
2.DimRB table with RB column

I have 2 measures, rev, rev%(calculations happen at run time). The values have been calculated on top of fact tables.

I need to fetch Mu , RB names from respective dimension tables where rev>10 and rev%>0.06.

I have written a dax , which works correctly for MU. But I also need to include RB in the same filter. 1st level filter should be MU, 2nd level filter should be RB, then check rev>10 and rev%>0.06 and return appropriate mu,rb.

DAX:

rev>10_rev%>5AcrossMUs =

CONCATENATEX(

FILTER(

VALUES(DimMU[MU]),

[Rev] > 10   && [Rev%] > 0.06)

),

DimMU[MU],

", "

)

Table data is like  -

DIm MU

 Muid MU 123 India 124 america 125 australia 126 canada 127 africa

RB table

 Reid Rb 1 iron 2 steel 3 plastic 4 textile

I also tried writing dax as -

rev_revper_mu_rb_2 =

VAR FilteredMU =

FILTER(

VALUES(DIMMU[MU]),

CALCULATE(

[Rev \$] > 10,

DIMMU

)

)

RETURN

CALCULATETABLE(

SUMMARIZECOLUMNS(

DIMMU[MU],

DimRB[RB],

"Total Rev", [Rev\$],

"Total Rev%", [Rev%]

),

FilteredMU,

FILTER(

VALUES(DimRB[RB]),

CALCULATE(

[REV%] > 0.06,

DimRB

)

)

)

#dax

1 ACCEPTED SOLUTION
Resolver I

Hi All, Well I got a fix.

Firstly, the measure "rev" was being calculated on top of Fact_A table and the measure "rev%"  was being calculated on top of Fact_B table. These 2 fact tables did not have a relationship with each other. And there was no common dimension table connected with these 2 fact tables. No common connection.

Secondly, I was trying to fetch MU  from DimMU and RB from DimRB tables. The 2 columns came from 2 different dimension tables and I wanted to filter on them together.

So what I had to do is-

- I had to create a new dimension table in modelling tab of power bi using below DAX-

CrossJoinTable =
CROSSJOIN(
DimMU,
DimRB
)

CROSSJOIN function in DAX generates a cross product of two tables, combining each row from the first table with each row from the second table.

• Manage Relationships (Optional): After creating the cross join table, you might want to disable relationships between Table1 and Table2 to avoid unintended behavior in your reports. You can do this by going to the Manage Relationships dialog.

- Now in this newly created calculated table, I create a column using DAX to concat MU and RB
dax:
-Concatatenate(CrossJoinTable[MU],concatenate("-",CrossJoinTable[RB]))=> which returned data in combination of india-textile,india-iron and so on..

Next
- In Fact_A table, wrote lookup function to fetch MU name, RB name, And then create a calculated column concatenating mu+Rb names same as above.
- In Fact_B table,write lookup function to fetch MU name, RB name, And then create a calculated column concatenating mu+Rb names same as above

dax:

MU_Name = LOOKUPVALUE(DimMU[MU],DimMU[MUid],Fact_A[MUid])
rb_Name = LOOKUPVALUE(Dimrb[rb],Dimrb[rbid],Fact_A[rbid])

MU_RB = CONCATENATE(Fact_A[MU_Name], CONCATENATE("-",Fact_A[RB_Name]))

Similar dax for Fact_B table.

-Now we have common column in all 3 tables with a combination of MU+RB column.

Now I created relationships -

- relation between  Fact_A and CrossJoinTable - many to 1, join condition MU+RB column.
-relation between  Fact_B and CrossJoinTable - many to 1,join condition MU+RB column.

Now I wrote Dax as follows -

rev_greaterThan_10_revPercent_greaterThan_5_Across_MU_RB =

CONCATENATEX(

FILTER(

VALUES(crossjointable[MU_RB]),

[Rev] >10 &&

[Rev%] > 0.06

),

crossjointable[MU_RB],

", "

)

3 REPLIES 3
Resolver I

Hi All, Well I got a fix.

Firstly, the measure "rev" was being calculated on top of Fact_A table and the measure "rev%"  was being calculated on top of Fact_B table. These 2 fact tables did not have a relationship with each other. And there was no common dimension table connected with these 2 fact tables. No common connection.

Secondly, I was trying to fetch MU  from DimMU and RB from DimRB tables. The 2 columns came from 2 different dimension tables and I wanted to filter on them together.

So what I had to do is-

- I had to create a new dimension table in modelling tab of power bi using below DAX-

CrossJoinTable =
CROSSJOIN(
DimMU,
DimRB
)

CROSSJOIN function in DAX generates a cross product of two tables, combining each row from the first table with each row from the second table.

• Manage Relationships (Optional): After creating the cross join table, you might want to disable relationships between Table1 and Table2 to avoid unintended behavior in your reports. You can do this by going to the Manage Relationships dialog.

- Now in this newly created calculated table, I create a column using DAX to concat MU and RB
dax:
-Concatatenate(CrossJoinTable[MU],concatenate("-",CrossJoinTable[RB]))=> which returned data in combination of india-textile,india-iron and so on..

Next
- In Fact_A table, wrote lookup function to fetch MU name, RB name, And then create a calculated column concatenating mu+Rb names same as above.
- In Fact_B table,write lookup function to fetch MU name, RB name, And then create a calculated column concatenating mu+Rb names same as above

dax:

MU_Name = LOOKUPVALUE(DimMU[MU],DimMU[MUid],Fact_A[MUid])
rb_Name = LOOKUPVALUE(Dimrb[rb],Dimrb[rbid],Fact_A[rbid])

MU_RB = CONCATENATE(Fact_A[MU_Name], CONCATENATE("-",Fact_A[RB_Name]))

Similar dax for Fact_B table.

-Now we have common column in all 3 tables with a combination of MU+RB column.

Now I created relationships -

- relation between  Fact_A and CrossJoinTable - many to 1, join condition MU+RB column.
-relation between  Fact_B and CrossJoinTable - many to 1,join condition MU+RB column.

Now I wrote Dax as follows -

rev_greaterThan_10_revPercent_greaterThan_5_Across_MU_RB =

CONCATENATEX(

FILTER(

VALUES(crossjointable[MU_RB]),

[Rev] >10 &&

[Rev%] > 0.06

),

crossjointable[MU_RB],

", "

)

Super User

@Sania-F , for client you need to have measure like

rev>10_rev%>5AcrossClients =
CONCATENATEX(
FILTER(
VALUES(DimClient[client name]),
[Rev] > 10 && [Rev%] > 0.06
),
DimClient[client name],
", "
)

Resolver I

Hi @amitchandak , I need to filter and display MU name, RB name together.

example -for MU India, Rb iron the rev is >10 and rev% is >0.06

The data is as below -

if I see rev and rev% only for MU ,  below is how data looks -
When I filter in dax based on MU, for rev>10 , 3 MUs satisfy condition. But rev% nothing is satisfying >0.06 condition. So my dax is returning blank.

 MU Rev Rev% India 36 0.045 australia 23 0.03 america 19 0.01 canada 10 0.02 africa 10 0.03

When I apply filter on both MU and RB level together, then rev> 10 and rev% >6 condition gets satisfied in my excel. But I need DAX formula to get same results in my Power BI. How can I do that?

 MU RB Rev Rev% India iron 12 0.08 steel 10 0.03 plastic 9 0.05 textile 5 0.02 australia iron 1 0.03 steel 4 0.01 platic 7 0.03 textile 11 0.04 america Iron 3 0.01 steel 1 0.06 plastic 8 0.02 textile 7 0.04 canada Same as above 4 categories 10 … africa Same as abov 4 categories 10 …

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.