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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rookarumba
Helper III
Helper III

Help-Disconnected Measure Filter

Hi Everyone,

 

I've been trying to figure out this issus for weeks but I seem to hit deadend all the time and hoping to get some help here.

I know that a disconnect table is needed but I just can't seem to make it work with:
Selectedvalue and Switch

 

Current Situation

 

2 Tables.

1- All the Product ID

2- Current Sales based on date and product id

Overview.png

 

I've written a measure to indicate of the 45 Products id, which are "Puchased' and "Not Purchased" by a respective store. And have the visual as a Table.

Table_visual.png

 

End Result

 

User can click on a silcer: "Purchased" and "Not Purchase".

Formula.png

The table will filter accordingly and display the product id that are either "Purchased' and "Not Purchase", based on the selection.

If no selection i done on the silcer, show all.

9 REPLIES 9
AkhilAshok
Solution Sage
Solution Sage

Why don't you try creating a Visual level filter (EnableSlicer = 1) on the Table based on the below measure:

 

EnableSlicer =
VAR SlicerValue =
    SELECTEDVALUE ( 'Helper Slicer'[Purchased|NotPurchase] )
VAR PurchaseFlag = [P|NP]
RETURN
    IF ( PurchaseFlag = SlicerValue || ISBLANK ( SlicerValue ), 1, 0 )

Assuming you already have [P|NP] measure added as a column in the table, and the values of [P|NP] is in sync with Disconnected slicer values, everything should work fine.

@AkhilAshok

 

thanks for the input. I tried your methods but i cant seem to get it to filter to just "Purchase" or "Not Purchase".

 

Not too sure what went wrong on this. I created a calculated column with 1 & 0 as input to match with the Disconnect table but it doesnt seem to work thou 😞

 

I put the latest file with the edits in.

 

https://www.dropbox.com/s/xi5wmxjo2xnkqeb/Disconnected_SilcerWmeasure_16feb.pbix?dl=0

 

appreciate any help here 

themistoklis
Community Champion
Community Champion

@Rookarumba

 

Cannot create a slicer from a measure.

 

Best you can do is write a Power Query formula for "Puchased' and "Not Purchased" and then put it in a slicer in the front end.

I hope it makes sense.

I know is measure is not possible as a slicer thus using a disconnected table might help.

I read it from the various blogs but the use cases are for calculation purposes.

 

PowerQuery might not work for me as I've quite a big data set and I've to pick based on store location.

As my files are quite huge, writing those extra logic might slow down the data model.

 

Writing a measure helps to reflect the status but not allow me to filter it.

I can get the user to sort it but that not a long-term solution.

 

 

 

 

Hi @Rookarumba,

 

If I understand your scenario correctly that you want to create a slicer to allow users click "Purchase" and “Unpurchase”.

 

What about creating the calculated column with the formula below?

 

Column = IF(CALCULATE(SUM(Table1[Sales]))>0,"purchase","unpurchase")

You could have a reference of the attachement.

 

If you still need help, please share a share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-piga-msft, Thanks, let me look at it first and get back to you when i'm back in the office tomorrow

 

Is not possible to do calculated column since "Purchase" & "unpurchase" is determined by Sales_File thus only "Purchase" can be created by calculated column.

 

There is a master product list file that have all the products and that determine if a customer has "Purchase" or didn't purchase the product. 

 

Let me look at my data model and masked the confidential details. I prob post it over the weekend

 

Thanks 🙂

Hi @Rookarumba,

 

Have you solved your problem?

 

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please share your data sample and your desired output so that we could help further on it.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-piga-msft, sorry for the late reply. Nope, the earlier solution didn't help with my case.

 

I'm preparing a sample data so i can send it over by the end of the week but is there a email i can send so i can send the original copy?

 

Sorry for the delay

@v-piga-msft

 

Super sorry for the delay. Here is the link

https://www.dropbox.com/s/s7fsnkz9mkuv0e5/Disconnected_SilcerWmeasure.pbix?dl=0

 

The scenario is, I've to create multiple year, side by side against the products (Like in the example)

And the "Purchase/UnPurchase" is refering to 2013, as that is our main focus for the existing year.

 

We need to create a filter for "Purchase/Unpurchase" for 2013 so user can quickly glance through or export the data which is being narrowed based on Purchase or Unpurchase

 

Hope that help explain my situation

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.