Get Value of field depending on value of 2 slicers

[pegmoran]

I have two slicers, one is YEAR and one is ID.   If the slicer values selected match the year and ID in a table, I want to return the associated AMT.  Something like this:

I created a measure for the selected ID and Selected Year

I have tried using a measure and a new column.  This is the code I used for the new column:

_AdminShare = IF(MYTABLE[id])=[Selected_id) && MyTable[YEAR])=[Selected_Year]),MyTable[Amt]),0)

It is probably pretty apparent I'm new to this, so any assistance would be great!

[v-kathullac]

Hi  @pegmoran ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya

[v-kathullac]

Hi  @pegmoran ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya

[v-kathullac]

Hi  @pegmoran ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya

[pegmoran]

I actually see the post (post 5) 🙂

[pegmoran]

Im sorry, I had replied a week or so ago but apparently it didn't post.  I ended up solving this on my own!  I appreciate the help!

[pegmoran]

Thank you, unfortunately that code returns no values for AdminShare

I did create a measure for the selected year and the selected ID and can see they are populated correctly.  Would there be a way to use those measures in a statement that basically says if the 'year measure' matches the year in Table2 and the 'ID measure' matches the ID in Table 2, display the Amount?

[v-kathullac]

Hi @pegmoran ,

can you check the below points to overcome the your issue.

1. You want to filter Table2 such that only rows where Year = [SelectedYear] and ID = [SelectedID] are considered, and then return the Amount from those rows.
2. Create a DAX measure

AdminShare =
CALCULATE(
MAX(Table2[Amount]), -- Change MAX to SUM or AVERAGE depending on your business logic
FILTER(
Table2,
Table2[Year] = [SelectedYear] &&
Table2[ID] = [SelectedID]
)
)

3.[SelectedYear] and [SelectedID] should be measures, often created using SELECTEDVALUE() to retrieve user input from slicers

SelectedYear = SELECTEDVALUE('YearTable'[Year])
SelectedID = SELECTEDVALUE('IDTable'[ID])

4.If the slicers are based on fields from other tables (e.g., YearTable, IDTable), make sure those tables have a relationship to Table2.

5.If no relationship exists between Table2 and your selection tables:

6.The measure will not filter properly. Consider creating relationships in the model (from YearTable[Year] to Table2[Year] and IDTable[ID] to Table2[ID]).

Regards,

Chaithanya.

[v-kathullac]

Hi @pegmoran ,

Thank you for reaching out to Microsoft Fabric Community Forum.

Below are the few steps that will resolve your issue let us know if you need anything else.

1)Create the slicers Make sure you have slicers for:
-MYTABLE[Year]
-MYTABLE[ID]

2)create the below measure
AdminShare =
VAR SelectedYear = SELECTEDVALUE(MYTABLE[Year])
VAR SelectedID = SELECTEDVALUE(MYTABLE[ID])

RETURN
CALCULATE(
MAX(MYTABLE[Amt]),
MYTABLE[Year] = SelectedYear,
MYTABLE[ID] = SelectedID
)
3)If your slicers come from different tables, make sure those tables are related to MYTABLE

Regards,

Chaithanya.

[SundarRaj]

Hi @pegmoran
Here, all of them Columns ( ID, Year and Amt ) are in the same table ( My_Table ). So creating the measures for Selected_ID and Year are not required. Simply creating the measure and selecting the required items in the slicer should work. 
Basically,
Amount = SUM ( MyTable[Amt] )
If you pull in the measure, with a certain ID selected && certain Year selected , this should work. Let me know if this works

[pegmoran · Accepted solution]

I actually figured it out.  I had to join my 2 tables on 2 columns and that fixed the issue!

[pegmoran]

I agree if this was a standalone process.  I'm actually adding it to a table that exists and in sql it was a subquery,  So leaving as is, it creates multiple rows and values, where it should just add to an existing row on the ID (i know it's a "joining" type issue)

[SundarRaj]

Would you be okay to share the source file on which you are working right now and preferably the image of what you'd want the end product to look like? Thanks