Forum Discussion

MikiD's avatar
MikiD
New Member
6 years ago
Solved

Calculated field and filtering

Hello, I have a doubt with a calculated field.   I have a TASK table with this information: TASK_VALUES (date, task, value) 1.2.2020, TASK1, 120 1.2.2020, TASK2, 30 1.2.2020, TASK3, 200 1.2.2...
Need Help
  • Stachu's avatar
    Stachu
    6 years ago

    If values need to change with slicer selection then you cannot use a calculated column, it has to be a measure.
    Few  changes are needed here:

    1) change relationships to what you see below, in visuals and formulas only use relating to tasks use only columns from 'TASK' table as it will propagate filter to TASK_DISTRIBUTION and TASK_VALUES

    it removes the bidirectional relationship (which is usually a bad practice, more details here https://www.sqlbi.com/tv/understanding-relationships-in-power-bi/ around 14:30)

    2) add this measure (it will work with relationships like above)

    Measure =
VAR __ValuesWithPercent =
    ADDCOLUMNS ( 'TASK_VALUES', "%", CALCULATE ( SUM ( 'TASK_DISTRIBUTION'[%F] ) ) )
RETURN
    SUMX ( __ValuesWithPercent, [Value] * [%] / 100 )

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Well, actually you can do that.

Drag to filter. Select the "Condition" tab. Suppose your paramater is named "Chosen department". You could create a condition like:

SUM(IF [department] = [Chosen department] THEN 1 END) > 0

This will basically get all the clients that have at least 1 sale on that chosen department