How to filter only specific column in table

[Anonymous]

Hi everyone, I'm working on a table, and I need to filter a data of specified range time in table. But not all the column, just a specific column. All the remaining column must keep current value. 

As attached below.
2 column (only Hour by Date and Cost By Date will be afftected by a created date filter).
When I select 01/01/2020 - 01/12/2020, the result is
-Client Id = 1001, Hours: 50, CostValue: 80, Hour by date: 30, cost value: 50 
- Client Id = 1002, Hours: 27, CostValue: 100, Hour by date : 15, cost value: 40
But now when I filter, all the data is affected. 
Any one can help me on this. Thank you so much.

[Anonymous]

Hi @Jihwan_Kim , Please visit the link to donwload, I don't know how to upload to this topic 
filter.pbix 

[Jihwan_Kim]

Hi,
Thank you for sharing.
I have looked into the file, but I was confused a bit about what you tried to achieve.
However, I tried to translate your initial question, which was that you do not want to filter the tables by a "CreateDate" slicer.
-Bottom Table: I think it is not affecting because there is no connection.
-Upper Table: I think the table and the slicer are coming from the same origin. So, if you do not want the slicer to affect it, please try to do it step by step as the below picture shows.

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hello, thank you so much. 
I know about the interaction for particular chart. Please look into the second table, 
I want to generate Hour By Date and Cost By Date from the the first table. And when I filter createdate, only 2 columns will be affected by filter.

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

Sorry to say that I quite do not understand how to calculate "Hour By Date" and "Cost By Date".

So, I assumed that those were Average Hours by Date and Average Cost by Date.

Based on my assumption, I created two calculated measures like below.

And, the link to my pbix file is down below.

Did I answer your question? Mark my post as a solution!

Avg Cost by Date =

VAR totalcosts =

CALCULATE (

SUMX (

Query1,

Query1[CostValue]

),

FILTER (

Query1,

SELECTEDVALUE ( 'Table'[ClientId] ) = Query1[ClientId]

)

)

VAR howmanydays =

CALCULATE (

COUNTROWS ( Query1 ),

FILTER (

Query1,

SELECTEDVALUE ( 'Table'[ClientId] ) = Query1[ClientId]

)

)

RETURN

DIVIDE (

totalcosts,

howmanydays,

BLANK ()

)

Avg Hour by Date =

VAR totalhours =

CALCULATE (

SUMX (

Query1,

Query1[Hours]

),

FILTER (

Query1,

SELECTEDVALUE ( 'Table'[ClientId] ) = Query1[ClientId]

)

)

VAR howmanydays =

CALCULATE (

COUNTROWS ( Query1 ),

FILTER (

Query1,

SELECTEDVALUE ( 'Table'[ClientId] ) = Query1[ClientId]

)

)

RETURN

DIVIDE (

totalhours,

howmanydays,

BLANK ()

)

https://www.dropbox.com/s/a8tqcs42nvemjf4/johncat%202021%200322.pbix?dl=0 

[Anonymous]

Hello, 
yah thank you so much,

Hour by Date, just show the cost in the date of range
I mean.
I want to show Hour for all of time, and Hour by date => hour by specified date( slicer). But just when select slicer , only 2 columns has been refreshed, total value is not allowed to refreshed.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Sorry that I did not clearly understand from the first time.

I hope this time I understood correctly.

The new link is down below.

Hour by Date V2 =

CALCULATE (

SUMX (

Query1,

Query1[Hours]

),

FILTER (

Query1,

SELECTEDVALUE ( 'Table'[ClientId] ) = Query1[ClientId]

)

)

Cost by Date V2 =

CALCULATE (

SUMX (

Query1,

Query1[CostValue]

),

FILTER (

Query1,

SELECTEDVALUE ( 'Table'[ClientId] ) = Query1[ClientId]

)

)

https://www.dropbox.com/s/nd95kfe9f3lzxne/johncat%202021%200322.pbix?dl=0 

[Anonymous]

Thank you for your help, really helpful for me. But, when for I drag a slicer with no value, the second table is empty. How can I keep the current value except 2 measures
can you download the file, I group by one more column.
https://drive.google.com/file/d/1X8oNoKc_fq_lpFkHeD4uxf-aZCS3zRki/view?usp=sharing

Thank you so much. Have a good day ahead

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I am not sure if I understand your question correctly.

I assume that your question is. if you select nothing in the bottom slicer, the up-table Avgcost / Avghour result is empty, right??

In this case, you have to change the measures. Because it depends on the selection from the slicer. If nothing is selected, then the calculation will be nothing. 

I don't know very well about your data, but I am allowed to suggest, please try to create the dim-table for your slicer. And change the measures.

Please check the below link.

https://www.dropbox.com/s/qjrbzivuas1ef65/testdata.pbix?dl=0 

[Anonymous]

yah, thanks for your advice. Your file is an example right?
https://www.dropbox.com/s/qjrbzivuas1ef65/testdata.pbix?dl=0 

In my file, when I select seperate date, it works fine now. But if I select all of dates. The measures are empty. Do you know the issue, do I need to add any filter. 

[Jihwan_Kim]

Hi, @Anonymous 
thank you for your feedback.

I am not sure, but in my opinion, try to check what does "selectedvalue( )" bring you for the result. 

It is a very useful function.

However, if there is more than one value selected, then the outcome is blank.

I saw in the measures that quite a lot of selectedvalue()  are used. 

So, if your slicer is selecting more than one, then the result is blank. 

I hope it helped.

Adding a filter and write a measure simply is one way.

However, I am not sure if there are some reasons why you prefer not to connect the tables, and use selectedvalues()

[Anonymous]

Thank so much for your information, I don't connect to table, because  I want to show all the value even it's empty.  😞

[Jihwan_Kim]

Hi, @Anonymous 

If it is OK with you, can you share the PBIX file?

Then, I can try to come up with a solution.

Thank you very much.