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

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.

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      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]
      )
      )