How to filter a table column with the selection on Other Table Column
Hi, In a tutorial we have seen the following report : In the report if Customer Name e.g. Adams Blake is selected the matrix shows the Brands which were purchased by customer along with sales amount. However, in the second slicer we could not see the product names which were purchased by Adams. As we know that a filter from Customer easily reaches to Sales ( for one to many relationship) but from Sales the same is not propagated to Product because of Many to One relationship and following is the screen shot of data model: In the tutorial we have seen that the relationship between Sales and Product was made Bidirectional and after that we achieved the desired report i.e. we can see the product names along with Brands when a specific customer is selected and following is the screen shot of the same: However it is advised that the bidirectional relationship should not be activated for the entire data model as the same can create many complications and unexpected results. Now the question is what will be the formula based solution to achieve the aforesaid situation where we can see Product names along with brands when a specific customer is selected. The same has not been told in tutorial but in practical scenario we can face such setuation and this will be very helpful for us if we get a code based solution of the same. Regards, Somnath
static grand total in matrix when filtered
Hi, I have a matrix with different countries as rows and Value (USD) as values. If I filter on a country, I want to keep the grand total unchanged. So in this case, if I filter on Zambia, I want it to show 103.55 for Zambia and 287,524.39 for Total. Is it possible?
DAX filtering with Slicers through multiple one to many related tables
Hi all, First time posting but any help would be greatly appreciated. I've reduced the model down to remove some complexities but still having issues pulling information correctly through tables. Schema is like a reversed star schema i.e. the fact table is the 1 on the one to many side relationship (this is the part i'm interested in): Programme table (1) => (*) Project table Project Table (1) => (*) Project Theme table Represented below with dubby data: Wider Schema In the main report I am using a table with the main element being ProgrammeReference and as expected when I use a slicer containing anything from project it doesn't filter and so I need some DAX in order to retreive the data. When I add the following visual level filter on report it filters the report BEFORE the slicer is used which isn't what I want: Linked project Cross filter slicer = CALCULATE(DISTINCTCOUNT(Programme[ProgrammeId]),CROSSFILTER(Project[ProgrammeId],Programme[ProgrammeId],Both)) So any help getting the report to filter only when the slicer is used would be very helpful. Slicer: Linked Project Stage, 'Project'[Project status] However I also need a slicer to filter the main report using fields from the Project theme table, but only after the slicer has been used, I've attempted writing some DAX of my own but it simply removes all fields from the report e.g. the below did not work: Project Theme Cross filter slicer = if( ISFILTERED('Project Theme'[ProjectThemeType]) = TRUE(), IF( Project[Linked project Cross filter slicer] > 0, CALCULATE(DISTINCTCOUNT('Programme'[ProgrammeId]),CROSSFILTER('Project Theme'[ProjectId],Project[ProjectId],Both)))) I had envisioned some kind of Measure would be required to be added as a visual level filter to the main report but open to other solutions. Let me know if more information is required! Thanks in advance Scott99How to create dax to count total by filtering/excluding a specific value from it
Hi I need to create a card that counts the number of cases by its phase. So for each Case Stream there is a Phase ID value related to the Phase, but one case stream has a different value (inverted meaning) in comparison of other streams, example: Phases Phase ID Case Stream Open 22 Family Open 3 Administrative Open 22 Commercial Open 5 Other In progress 3 Family In progress 22 Administrative In progress 3 Commercial In progress 6 Other As you can see Administrative Case Stream has is special as its value means a different phase, so for example if I need a dax to count total number of cases that are "Open" I would like to exclude Phase ID 22 just for "Administrative" as for this case means In progress (not open). Can someone help me how would be the dax based on this example? Thank you
