Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to filter only specific column in table

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.  ...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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]
    )
    )
     
     
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Jihwan_Kim
      Super User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.