Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mizaskun
Helper II
Helper II

DAX measure to filter a table

Good afternoon,

 

I have two tables: a sales table and a table with some data to filter the sales table. This architecture is a must.

 

The idea is to filter the sales table depending on the filter applied on the permission table, which gets automatically filtered when starting the report.

 

SALES TABLE

# | Price | User | Shop

1 | 10 € | A | Paris

2 | 12 € | A | Bordeaux

3 | 11 € | B | Rome

4 | 10 € | C | Paris

 

PERMISSION TABLE

USER | ACCESS | TYPE

A | A | user

B | B | user

C | C | user

C | Paris | shop

 

So that user A would see sale n1 and sale n2. User B would see sale n3 and user C would see sale n1, sale n4.

 

The idea is to calculate it dinamically. Thus, I need this to be done on DAX with a measure. I was thinking to calculate an "X" if the user or the shop is found in the permission table, and filter the sales table by "X".

 

Any idea on how to do this? 

 

I cannot use a DAX table due to the fact that it needs an update of the report to get updated. 

 

Thank you so much! 

 

1 ACCEPTED SOLUTION

Hi @colacan 

 

I finally did it with the following measure:

 

Valid =
   min(
      calculate(
         distinctcount(SALES[sn]),
         USERELATIONSHIP(SALES[shop], PERMISSION[access])
      )
      +
      calculate(
         distinctcount(SALES[sn]),
         USERELATIONSHIP(SALES[user], PERMISSION[access])
      )
, 1)
 
This makes the rows to be seen get a 1, and the rows with no permission disapear. 

View solution in original post

6 REPLIES 6
colacan
Resolver II
Resolver II

It is strongly recommended to make the the permission table has unique user. it is to prevent any unexpected error in the future as well. if you can not change exisitng permission table, it's better to create one and relates it to both sales table and existing table as 1:m (in case you need exisitng permission table in the future)

How could I do a user to see his sales & the sales of the shops he owns? thanks! 

@mizaskun  Hi, based on your sample data (I have to depend on it to answer your question since this is the only source with which I can understand your issue) I can see the shop is in the sales table already. in that can each user can see their number by shop by simply adding Sales[shop] in the visual as below;

colacan_0-1633627592266.png

 

Hi @colacan 

 

I finally did it with the following measure:

 

Valid =
   min(
      calculate(
         distinctcount(SALES[sn]),
         USERELATIONSHIP(SALES[shop], PERMISSION[access])
      )
      +
      calculate(
         distinctcount(SALES[sn]),
         USERELATIONSHIP(SALES[user], PERMISSION[access])
      )
, 1)
 
This makes the rows to be seen get a 1, and the rows with no permission disapear. 
colacan
Resolver II
Resolver II

@mizaskun Hi,

 

I assume the permission table and sales table is related as 1:m by the User

 

If I explain based on your table, you may create basic mearues which you need, like sum of price or average price

Sum_Price = sum(Sales[Price])
AVG_Price = average(Sales[Price])
 
Then, the filtered calculation based on user would be as below;
Sum_Price_PerUser =
IF(HASONEVALUE(Permission[User]),
CALCULATE([Sum_Price],
FILTER(Sales,related(Permission[User]) = SELECTEDVALUE(Permission[User])
)
),
BLANK()
)
 
By simply replacing the basic measure from [Sum_price] to [AVG_Price] you can get user-filtered average price as below
 
Avr_Price_PerUser =
IF(HASONEVALUE(Permission[User]),
CALCULATE([AVG_Price],
FILTER(Sales,related(Permission[User]) = SELECTEDVALUE(Permission[User])
)
),
BLANK()
)
 
These measure will show blank if there is no specific user selected from Permission table. 
 
colacan_0-1633616019596.png

 

Thanks 
 
Please mark this as solution if this helped.
 

The relation is **, many to many. Any other way to achieve this? 

 

Thanks! 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors