SUMMARIZE with FILTER and SELECTEDVALUE

[JoaqBI]

Hi, I am trying to use CALCULATETABLE and SUMMARIZE on a table, using a SELECTEDVALUE as a filter :

TableTesting2 =

CALCULATETABLE(

    SUMMARIZE(

    Proj_CostSummaries,

    Proj_CostSummaries[TS_Total_Cost_Project_Currency],

    Proj_CostSummaries[Workstage_Description]

    ),

     FILTER (Proj_CostSummaries, Proj_CostSummaries[Project_Number] = SELECTEDVALUE(Proj_CostSummaries[Project_Number]))

)

If I change the text in red for "00123" or any other valid Project number it works. However if I try using a SELECTEDVALUE it does not filter anything.
I have displayed this selectedvalue in a card and it is working, so it shows the project number selected from the slicer.

Any ideas why this may not work ? Thanks a lot!

[Varad_Phatak]

Facing the same issue. Anyone able to find solution?

[logu_cbe]

Am also facing same issue. Did you get solution for this issue? 

[Anupam]

Hi @JoaqBI , Are you able to find solution to this. I am facing same issue.

[Mikelytics]

Hi @JoaqBI 

Do you refer to the same colum  which is oin your slicer. Are you sure that if you select the slicer value that there is everytime only one unique Project_number behind it? 

Can you please try to create a test measure 

SELECTEDVALUE(Proj_CostSummaries[Project_Number])

then put this test emasure in a Card visual and place it on the same page. Does it show the project number expected?

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[JoaqBI]

Hi Mike,

I tried and it doesn't work. I did a simpler Proj_CostSummaries table so I didn't need to share private data and attached the following 'Test' file.

You can see that if I change __SelectedValue for "00124" I could see the table properly filtered. However if I tried with either __SelectedValue or CurrentProject, it does not show anything.
Any ideas? Perhaps with the file (Super simple file) is easier to check.

Thanks a lot again!
Test File - Dropbox 

[Mikelytics]

HI @JoaqBI 

Unfortunately due to security reasons I can not download files from public storage.

But in general the reason why SELECTEDVALUE does not work is when based on your selection there is not one unique value in the column fwhich you used in SELECTEDVALUE(). 

Can you please the sample data as a table in the post window?

Or another way. Can you please create a table and put in the table the column which you have used in SELECTEDVALUE. Then use the slider and show me the values which you can see in the table.

Best regards

Michael

[JoaqBI]

Hi Mike,
Thanks a lot. I understand.

The Test file is this simple :

1.- I have a table with the columns Project_Number.

2.- I have a card showing CurrentProject.
CurrentProject = SELECTEDVALUE(Proj_CostSummaries[Project_Number])

3.- I have 'TableTesting' with the DAX mentioned before.

TableTesting =

VAR __SelectedValue = SELECTEDVALUE(Proj_CostSummaries[Project_Number])

RETURN

CALCULATETABLE(

    SUMMARIZE(

    Proj_CostSummaries,

    Proj_CostSummaries[Cost],

    Proj_CostSummaries[Workstage_Description]

    ),

     FILTER (Proj_CostSummaries, Proj_CostSummaries[Project_Number] = __SelectedValue)

)

 

When I click one of the three projects, the card displays the project number which is correct. The TableTesting shows no values, no matter if I select or not any specific project.

If I change the code for TableTesting with the filter to a specific project , "00124" for example, rather than filtering with SELECTEDVALUE, it works.

I assume that SELECTEDVALUE is not working properly within this filter. Eventhough it works properly to display to a card using the measure CurrentProject. Screenshot below of the situation described above :

[nsexton12]

Have you tried creating a relationship between the common columns in the two tables? I.e. workstage description? This seemed to resolve the issue for me. 

If this worked for you please mark as a solution!

[Mikelytics]

@JoaqBI 

Can you please put this table on the page where you have set up everything?

If on your page this table shows more than one value then this is the reasong fpr your problem.

Best regards

Michael

[JoaqBI]

Hi Mike,
The table does actually have the same project number more than once, screenshot below.

Do you think this is the issue ?

Is it possible to filter this table so I only see all the rows with Project_Number = SELECTEDVALUE.. for example, and I also see the workstage description column and the cost column/Sum of Cost ?

Thanks!

[Mikelytics]

Hi @JoaqBI 

same project number multiple times is not a problem. A problem only comes when you have different values in the current filter context because SELECTEDVALUE only gives back a value when there is one unique one in the current filter context.

BUT I looked on your initial request again and to be honest I was a little bit to quick with my initial suggestion. THe thing is you want to calculate a table and not a measure. and this is the problem.

So I have some other questions:

1) DO you want to calculate a table which is used inside of a measure or do you want to calculate a table which ist then stored in the data model?

2) what do you try to achieve with this? so what is the actual business problem. I understand you want to summarize a table but what is the reason for the second part?

Best regards

Michael

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[JoaqBI]

Hi Mike,

Yes, the aim is to calculate a table filtering data from this other table.

The reason is to be able to display each workstage with their associated cost for a selected project. I am doing a simple project cost summary page.

The way the data is organised in Proj_Summaries is that you have a row with a project name, workstage, cost and period of time. So for each period of time on a stage you have a row. This is the reason why project number is repeated a few times within the table.
I am looking to filter that so I can show all the rows for one project only, the workstage and the sum of cost for that workstage. 
If the filter is specific (i.e "00124" ) it works. If the filter is a selectedvalue it doesn't. 
What confuses me is that when you display the selected value on a card it seems to be "00124" so I am not sure how to solve this.

Thank you once again. 

[Mikelytics]

Hi @JoaqBI 

Please try to put SELECTEVALUE as separate variable

TableTesting2 =
VAR __SelectedValue = SELECTEDVALUE(Proj_CostSummaries[Project_Number])

RETURN

CALCULATETABLE(
    SUMMARIZE(
    Proj_CostSummaries,
    Proj_CostSummaries[TS_Total_Cost_Project_Currency],
    Proj_CostSummaries[Workstage_Description]
    ),
     FILTER (Proj_CostSummaries, Proj_CostSummaries[Project_Number] = __SelectedValue)
)

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[JoaqBI]

Hi Michael,

Thanks for your quick response. I have tried and it's still empty :

However if I tried to replace in the filter __SelectedValue with a fixed value for a project number, in this case "00440" it works :

[Aradhana]

Hi @JoaqBI and @Anupam ,

Did you get a solution to this problem. Even I'm facing the same issue.

Thanks,

Aradhana

[gscicluna]

I am having the exact issue as well. Any reoslution?