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

[Sania-F]

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 

[Sania-F · Accepted solution]

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

        ", "

    ) 

[amitchandak]

@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],
", "
)

[Sania-F]

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	…

Thankyou in advance for your help.