topic Re: Create Seperate Visual based on different column lookup in DAX Commands and Tips

Create Seperate Visual based on different column lookup

Nou_admin1 — Mon, 28 Feb 2022 11:58:16 GMT

Hi Team,

Please suggest me a idea how to arrive solution for my working.

I have a Data sheet which contains the data for Managers, Executive and Reps in a single sheet with continues column with there respective name and code and not differenciate the Designation in data sheet. But I have a Master Table with Designation wise reporting list. Based on this Master Data I have Filtered the data for Reps with the below Hierarchy order.

Manager - (slicer with Single Selection)

Executive (Slice with Multi selection)

based on this both slicer the Rep Value details will show in a visual.

Now I want to Show a Visual Screen only for Executive Data which is filtered in the Executive slicer screen. But I am getting the Sum of Reps data in that visual. How to avoid Reps Data in that visual and show only the Selected Executive data.

Note - In Data Sheet contains 8 nos of Column with Numeric Value.

Please suggest me a Idea of DAX Measurement for create .

Thanks

With Regards,

Ashok Babu

Re: Create Seperate Visual based on different column lookup

amitchandak — Mon, 28 Feb 2022 12:24:51 GMT

Nou_admin1 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Create Seperate Visual based on different column lookup

Nou_admin1 — Tue, 01 Mar 2022 11:09:15 GMT

Hi Amit,

Thanks for your reply.

Here with I am attached the Download link of some Sample Data and Output Format. Please check and kindly suggest a suitable solution. Total 4 Sheet available 2 is Data sheet and 1 is master data based on master data the output needs to load as 1 sheet is for output sheet.

Download link

https://wetransfer.com/downloads/e1ea943aa1c06f70202fe6c645b340d720220301104911/eccc9c418a311d11248980c163fa7f8e20220301104925/b9b0fe

Thanks.

Re: Create Seperate Visual based on different column lookup

tamerj1 — Wed, 02 Mar 2022 05:04:17 GMT

Hi Nou_admin1
Here is your file with the solution https://www.dropbox.com/t/eFiNfMRp7efEcjc3
First you need to have a date table. In the Master Table you need to have an FF MGR Emp. Code column. Then you can set your data model as follows

The rest is simple. Here are samples of the required measures

Call Avg = IF ( HASONEVALUE ('Review Sheet'[Call Avg] ), VALUES ('Review Sheet'[Call Avg] ) )

MGR Call Avg = CALCULATE ( [Call Avg], USERELATIONSHIP ( 'Master Data'[FF MGR Emp. Code],'Review Sheet'[Emp Code] ), CROSSFILTER ( 'Master Data'[Emp. Code], 'Review Sheet'[Emp Code], None ) )

Your report looks like this

Please let me know if this answers your query. Thank you!

Re: Create Seperate Visual based on different column lookup

Nou_admin1 — Wed, 02 Mar 2022 05:33:47 GMT

Super, Thank you So Much tamerj1 - I got the Perfect Solution.

Let I apply the same in my Real Data and Generate the Report. If need any more help I will ping you.

Once Again Thanks to tamerj1 amitchandak

Re: Create Seperate Visual based on different column lookup

tamerj1 — Wed, 02 Mar 2022 05:39:30 GMT

Nou_admin1

Sure anytime.

Re: Create Seperate Visual based on different column lookup

Nou_admin1 — Wed, 02 Mar 2022 07:52:12 GMT

Hi,

I am tried in my Actual Data the First Measure is not working properly. While apply the measure the Field Force Name is coming correct but the Data is showing the second measure value for all Field Force Name's as per the filtered conditions.

If I try same like as second measure for the first measure then the data is showing correct value. So I am used this way. Is there any wrong by doing this way?

Thanks

Re: Create Seperate Visual based on different column lookup

tamerj1 — Wed, 02 Mar 2022 08:20:48 GMT

It is also fine this way. It seems you have switched between the active and inactive relationships. You can do it either ways. But in order not to make mistakes I recommend you double check your relationships to confirm identidal to the sample file.

Re: Create Seperate Visual based on different column lookup

Nou_admin1 — Wed, 02 Mar 2022 08:45:39 GMT

Yes, you are Right. Now all Perfect. I did mistake in Relationship mapping and now corrected.

Thank you So much

Re: Create Seperate Visual based on different column lookup

tamerj1 — Wed, 02 Mar 2022 09:04:20 GMT

Nou_admin1
Great!
If you are satsfied with the result, I would kindly request you to mark my reply as accepted.
Thank you!