Filter values from one table in another without relationship DAX

[varung8899]

Hi All, I have 3 tables DIM_USER, DIM_ACT & FACT_ACTUALS. I would like to filter values in FACT_ACTUALS for matching column values ([LEVEL] & [LABEL] & [SOURCE] & [CC] & [ORG]) in DIM_ACT. There is NO relation between DIM_ACT & FACT_ACTUALS. I cannot relate with M:M relation and don't want to, due to ALL values present in columns of DIM_ACT table which is not in present in FACT_ACTUALS.

DIM_USER has below columns.

ID

EMAIL

DIM_ACT has below columns.(Related to DIM_USER using ID)

PATH

GROUP

ID

LEVEL

LABEL

SOURCE

CC

ORG

LOC

The values in above columns in DIM_ACT could be multiple meaning there could be N number of values separated by | symbol. Eg: LEVEL column can have a row value 25|79|44 and user should only be able to see these values under LEVEL from FACT if they exist in FACT. If ALL exists then all values in FACT should be visible.

FACT_ACTUALS has below columns.(NOT RELATED TO ANY)

LEVEL

LABEL

SOURCE

CC

ORG

LOC

Is there a way to FILTER values in FACT_ACTUALS based on logged in user ID & matching column values

([LEVEL] & [LABEL] & [SOURCE] & [CC] & [ORG]) in DIM_ACT using DAX without establishing a relationship ? TIA.

[talespin]

Hi @varung8899 

Based on your file, one way I could think of doing it is this. Create a measure and apply as filter on your table visual(Screenshot).

FilterFACT =

VAR _Level = SELECTEDVALUE(FACT_ACTUALS[LEVEL])

VAR _Lable = SELECTEDVALUE(FACT_ACTUALS[LABEL])

VAR _Source = SELECTEDVALUE(FACT_ACTUALS[SOURCE])

VAR _CC = SELECTEDVALUE(FACT_ACTUALS[CC])

VAR _ORG = SELECTEDVALUE(FACT_ACTUALS[ORG])

VAR _IsMatch =  CALCULATE(

                            COUNTROWS(DIM_ACT),

                            CONTAINSSTRING(DIM_ACT[LEVEL],_Level),

                            CONTAINSSTRING(DIM_ACT[LABEL],_Lable),

                            CONTAINSSTRING(DIM_ACT[SOURCE],_Source),

                            CONTAINSSTRING(DIM_ACT[CC],_CC),

                            CONTAINSSTRING(DIM_ACT[ORG],_ORG)

                        )

RETURN IF( _IsMatch > 0, 1, 0)

--------------------------------------------------------------------------------------------------

For the Sales Sum or Margin Sum, use this measure and replicate for Margin.

Sum Sales = SUMX(

                    FACT_ACTUALS,

                    VAR _Exists = [FilterFACT]

                    RETURN IF( _Exists = 1, FACT_ACTUALS[SALES], 0)

                )

-----------------------------------------------------------------------------------------------

[varung8899]

@talespin Please ignore the slicers I have placed in the report. I have just placed it for reference in future. These are my queries 1.) How to pass all these column values ([LEVEL] & [LABEL] & [SOURCE] & [CC] & [ORG] from DIM_ACT individually using DAX and match respective columns in FACT_ACTUALS table ? Values in columns of DIM_ACT are separated by semicolon and I need to match one by one in respective columns in FACT_ACTUALS because FACT_ACTUALS will only have individual values under LEVEL and not values separated by semi-colon.

2.) FACT table does not have values as ALL hence I would like to filter those columns having value as ALL meaning it should display all the available values under this column (no filter should be applied to this column). Eg: for user SDK@abc,com, he should see all the LEVEL = 100 & A20 & K40 and labels should only be those values under LEVEL and not all the values from FACT_ACTUALS. There will be only a single Table visualization displaying all the columns in FACT_ACTUALS. When user logins he should see only those values assigned in DIM_ACT and not all the values under LEVEL & other columns mentioned. Hope using [LABEL] & [LEVEL] & [SOURCE] & [CC] & [ORG] & [LOC] will accomplish this but not for ALL values. Please let me know for any clarification. TIA.

[talespin]

hi @varung8899 

Please check this

https://drive.google.com/file/d/1z_hLHXVPK42EycvqKGf-fkkE6IZweKdv/view?usp=sharing

Changed measure to accomodate UserID.

FilterFACT =

VAR _User = "REN@abc.com" --USERPRINCIPALNAME() Use this, I do not have a user name to test this so hardcoding user.

VAR _Level = SELECTEDVALUE(FACT_ACTUALS[LEVEL])

VAR _Lable = SELECTEDVALUE(FACT_ACTUALS[LABEL])

VAR _Source = SELECTEDVALUE(FACT_ACTUALS[SOURCE])

VAR _CC = SELECTEDVALUE(FACT_ACTUALS[CC])

VAR _ORG = SELECTEDVALUE(FACT_ACTUALS[ORG])

VAR _IsMatch =  CALCULATE(

                            COUNTROWS(DIM_ACT),

                            DIM_ACT[EMAIL ID] = _User,

                            CONTAINSSTRING(DIM_ACT[LEVEL],_Level) || CONTAINSSTRING(DIM_ACT[LEVEL],"ALL"),

                            CONTAINSSTRING(DIM_ACT[LABEL],_Lable) || CONTAINSSTRING(DIM_ACT[LABEL],"ALL"),

                            CONTAINSSTRING(DIM_ACT[SOURCE],_Source) || CONTAINSSTRING(DIM_ACT[SOURCE],"ALL"),

                            CONTAINSSTRING(DIM_ACT[CC],_CC) || CONTAINSSTRING(DIM_ACT[CC],"ALL"),

                            CONTAINSSTRING(DIM_ACT[ORG],_ORG) || CONTAINSSTRING(DIM_ACT[ORG],"ALL")

                        )

RETURN IF( _IsMatch > 0, 1, 0)

[varung8899]

Thanks a ton @talespin This solution works perfectly. Only thing is that I am unable to test this as a role or another user when trying via Power BI Web. My intention is exactly to provide access via the table itself going forward and not add users manually in RLS however as part of data testing I am unable to do so by defining this measure under FACT_ACTUALS table in Manage Roles section. Its giving me error in my visual while viewing as a Test role. Do you have any thoughts pls ?

[talespin]

hi @varung8899 

A word of Caution, what you are doing in not RLS. If you want to restrict access to data, use RLS rather than relying on a measure to filter your data.

You need to change your Datamodel, Role should directly filter Users.

[varung8899]

Thanks for your valuable guidance @talespin . May I pls have a final ask to close this thread. If I have a column called GROUP in DIM_ACT having different group names for each user & multiple users can be tagged to the same group in future, could you please let me know what will be your approach to tweak the above measure and create a role in RLS ?  Appreciate your perseverance. TIA.

[talespin]

hi @varung8899 

What you are asking is more of RLS and Data Model question and not DAX. If you have your Data Model right, you do not/should not need this DAX to filter data for RLS. Think/Take help on how should you design your data model in this case.

[varung8899]

I hear you @talespin . The main reason why I went for DAX is because of M:M relationship between columns in my tables and I did not have another option than to go ahead with this approach in such a short time.  

[talespin]

hi @varung8899 

Please check this file, try to do something like this, there is probably better way to implement this, this is just to explain what I mentioned above.

https://drive.google.com/file/d/1Q_5OnCyLAgNW6soM5L1ek0o2kfQw45wE/view?usp=sharing

Viewing it as ABC@abc.com shows me only data that this user is meant to see. I have hard coded this user in roles filter as I do not have personal PBI service account. This is just a demo to guide you in right direction.

Data Model

[varung8899 · Accepted solution]

Hi @talespin  Please see the solution below. As you mentioned earlier creating a measure using the formula wasn't the right way and creating a role and applying RLS was the right approach. Since no one was able to provide a RLS DAX query for this requirement we have itself come up with a DAX solution to achieve the requirement. Please see below. 

1.) All semi-colon values in DIM_ACT columns must be replaced by "|" symbol for this formula to work. Either change the source data or create a calculated column to replace ";" with "|".

2.) Eliminated DIM_USER table and brought in EMAIL ID column to DIM_ACT table itself. 
3.) There is only 1 record for a single user in DIM_ACT under a single GROUP.
4.) Create a ROLE in Manage roles and apply below DAX under FACT_ACTUALS table. Using the below DAX , when the user logs in he will be able to see only the values separated using "|" in the respective columns of DIM_ACT and when ALL is present in any of the columns, he will be able to see all the values available in FACT ACTUALS.   

/* FILTER DIM_ACT TO FETCH THE RECORD HAVING ONLY THE LOGGED IN USER EMAIL ID & VALUES ASSIGNED UNDER LEVEL COLUMN */

&& [LEVEL]
IN

(
VAR _LEV =
MAXX (
FILTER
( 'DIM_ACT',
[EMAIL ID] = USERPRINCIPALNAME () ),
'DIM_ACT'[_LEV]
)

/* TAKE THE PATH LENGTH OF LEVEL COLUMN. THIS IS DONE BECAUSE THERE COULD BE N NUMBER OF VALUES SEPARATED BY | SYMBOL IN DIM_ACT TABLE
AND WE NEED TO SEPARATE THEM AS INDIVIDUAL VALUES */

VAR _LEVLEN = PATHLENGTH (_LEV )

/* CREATE A VIRTUAL TABLE WITH COLUMN NAME LEVLIST HAVING THE LIST OF LEVEL COLUMN VALUES */

VAR _LEVTABLE = ADDCOLUMNS ( GENERATESERIES ( 1, _LEVLEN ), "LEVLIST", PATHITEM ( _LEV, [Value] ) )

/* SELECT THE COLUMN VALUES AND PASS THE VALUES TO LIST */

VAR _LEV_list = SELECTCOLUMNS ( _LEVTABLE, "LEVEL", [LEVLIST] )

/* ADD ALL THE LEVEL VALUES FROM FACT_ACTUALS. THIS IS FOR A USER WHO HAS VALUE "ALL" ASSIGNED TO HIM UNDER LEVEL IN DIM_ACT TABLE */

VAR _ALLFACTLEV = ADDCOLUMNS ( VALUES ( FACT_ACTUALS[LEVEL] ), "ALL", "ALL" )

/* RETURN ONLY THE VALUES ASSIGNED IN LEVEL COLUMN IN DIM_ACT FROM FACT TABLE FOR LOGGED IN USER OR RETURN ALL THE VALUES FROM FACT TABLE WHEN THE LEVEL COLUMN IS PROVIDED AS ALL */

RETURN
SELECTCOLUMNS
(
FILTER ( _ALLFACTLEV, [LEVEL] IN _LEV_LIST || [ALL] IN _LEV_LIST ),
[LEVEL]
)
)

&& [LABEL]
IN (
VAR _LAB =
MAXX (
FILTER ( 'DIM_ACT', [EMAIL ID] = USERPRINCIPALNAME () ),
'DIM_ACT'[LABEL]
)
VAR _LABLEN = PATHLENGTH ( _LAB )
VAR _LABTABLE =
ADDCOLUMNS ( GENERATESERIES ( 1, _LABLEN ), "LABLIST", PATHITEM ( _LAB, [Value] ) )
VAR _LAB_LIST =
SELECTCOLUMNS ( _LABTABLE, "LABEL", [LABLIST] )
VAR _ALLFACTLAB =
ADDCOLUMNS ( VALUES ( FACT_ACTUALS[LABEL] ), "ALL", "ALL" )
RETURN
SELECTCOLUMNS (
FILTER ( _ALLFACTLAB, [LABEL] IN _LAB_LIST || [ALL] IN _LAB_LIST ),
[LABEL]
)
)

&& [SOURCE] IN ------ Similarly repeat the above code for other columns in DIM_ACT

[123abc]

Yes, you can filter values in FACT_ACTUALS based on matching column values in DIM_ACT using DAX without establishing a relationship between the tables. You can achieve this by using DAX functions such as FILTER and RELATED.

Here's a sample DAX formula that you can use to filter FACT_ACTUALS based on matching column values in DIM_ACT:

Filtered_FACT_ACTUALS =
FILTER (
FACT_ACTUALS,
COUNTROWS (
FILTER (
DIM_ACT,
DIM_ACT[LEVEL] = FACT_ACTUALS[LEVEL]
&& DIM_ACT[LABEL] = FACT_ACTUALS[LABEL]
&& DIM_ACT[SOURCE] = FACT_ACTUALS[SOURCE]
&& DIM_ACT[CC] = FACT_ACTUALS[CC]
&& DIM_ACT[ORG] = FACT_ACTUALS[ORG]
)
) > 0
)

This formula filters the rows in FACT_ACTUALS where there exists a matching row in DIM_ACT based on the specified column values ([LEVEL], [LABEL], [SOURCE], [CC], [ORG]). The COUNTROWS function counts the number of rows returned by the inner FILTER function, and if the count is greater than 0, it means there is a match, so the row from FACT_ACTUALS is included in the result.

You can create a new calculated table or measure using this DAX formula depending on your specific requirements.

Make sure to adjust column names and references according to your actual table structure.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[varung8899]

Thank you for your immediate reply. However when I tried to create a new measure using the above query it says "Multiple columns cannot be converted to scalar value". I think its because my DIM_ACT contains multiple rows with same ID & LEVEL. It works while creating a New Table but when I tried to include additional filter using USERPRINCIPALNAME(), it throws below error. Could you pls suggest a way around ?  

[123abc]

If your DIM_ACT table contains multiple rows with the same ID and LEVEL combination, it can cause issues when trying to create a measure that expects a scalar value. In such cases, you might need to aggregate or summarize the data to ensure that a single value is returned for each combination of ID and LEVEL.

Here's how you can modify the DAX formula to aggregate the data from DIM_ACT before filtering FACT_ACTUALS:

Filtered_FACT_ACTUALS =
FILTER(
FACT_ACTUALS,
COUNTROWS(
SUMMARIZE(
DIM_ACT,
DIM_ACT[ID],
DIM_ACT[LEVEL],
DIM_ACT[LABEL],
DIM_ACT[SOURCE],
DIM_ACT[CC],
DIM_ACT[ORG]
)
& FILTER(
DIM_ACT,
DIM_ACT[LEVEL] = FACT_ACTUALS[LEVEL] &&
DIM_ACT[LABEL] = FACT_ACTUALS[LABEL] &&
DIM_ACT[SOURCE] = FACT_ACTUALS[SOURCE] &&
DIM_ACT[CC] = FACT_ACTUALS[CC] &&
DIM_ACT[ORG] = FACT_ACTUALS[ORG] &&
DIM_ACT[USER] = USERPRINCIPALNAME()
)
) > 0
)

In this modified formula, we use the SUMMARIZE function to aggregate the data from DIM_ACT based on the columns ID, LEVEL, LABEL, SOURCE, CC, and ORG. This helps in ensuring that only unique combinations of these columns are considered.

Then, we apply the filter conditions to both the aggregated data and the original DIM_ACT table. Additionally, I've included a filter condition based on the USERPRINCIPALNAME() function to filter rows based on the logged-in user.

This approach should help you avoid the "Multiple columns cannot be converted to scalar value" error and apply additional filtering based on the logged-in user.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[varung8899]

Thank you. It says Syntax error while trying this code. Not sure if I am doing something incorrectly. Please see my exact requirement below with table structure below.

DIM_ACT

EMAIL ID	LEVEL	LABEL	SOURCE	CC	ORG	LOC
SDK@abc.com	100;A20;K40	XKG;TBF	1	E250;V365	25;36	ALL
REN@abc.com	100;A20;K40	XKG;TBF	ALL	E250;V365	25;36	ALL
OBQ@abc.com	ALL	ALL	ALL	E250;V365	25;36	ALL
ABC@abc.com	ALL	ALL	2	E250;V365	ALL	ALL
XYZ@abc.com	300:J99	TUT	2	ALL	58	ALL
DEF@abc.com	300	DUC	2	ALL	45	300;450

FACT ACTUALS contains columns LEVEL, LABEL,SOURCE,CC,ORG,LOC,SALES,STOCK.

When SDK@abc.com is logged in, I would like to see the matching column values in FACT_ACTUALS as defined in DIM_ACT. In the above table I have a single record for each user. I think when I transform columns using Delimiters then each user has multiple records creating issues hence I will not perform any transformation and keep DIM_ACT as above. My queries are 1.) Is it possible to pass all these column values ([LEVEL] & [LABEL] & [SOURCE] & [CC] & [ORG] from DIM_ACT individually using DAX and match respective columns in FACT_ACTUALS table ? Values in columns of DIM_ACT are separated by semicolon and I need to match one by one in respective columns in FACT_ACTUALS because FACT_ACTUALS will only have individual values under LEVEL and not values separated by semi-colon.

2.) FACT table does not have values as ALL hence I would like to filter those columns having value as ALL meaning it should display all the available values under this column (no filter should be applied to this column). Eg: for user SDK@abc,com, he should see all the LEVEL = 100 & A20 & K40 and labels should only be those values under LEVEL and not all the values from FACT_ACTUALS. There will be only Table visualization displaying all the columns in FACT_ACTUALS. When user logins he should see only those values assigned in DIM_ACT and not all the values under LEVEL & other columns mentioned. Hope using [LABEL] & [LEVEL] & [SOURCE] & [CC] & [ORG] & [LOC] will accomplish this but not for ALL values.

3.) In future If I plan to create slicers for LEVEL, LABEL, SOURCE,CC, ORG,LOC columns can I display only assigned values in DIM_ACT to the user (not all values from FACT_ACTUALS (like a hierarchical filter) to filter FACT_ACTUALS. All columns are Text format. Please let me know for any clarification. Appreciate all your help so far. TIA.