locked
effect of slicing un related table on facts RRS feed

  • Question

  • Hi eb

    Unlike traditional multi dimensional olap i see that slicing a table that is not connected to facts in a pivot report does not empty that fatcs measures. So no need to worry about this situation unless i do want this kind of behavior...

    So if i do want to force unrelated table filterriing to empty measures in a fact table 

    I should do it explicitly with calculations over those measures that will empty them if some unrelated table is filterred?

    TIA!

    Rea

    Tuesday, September 2, 2014 9:45 AM

Answers

  • Thanks

    I was thinking along more simple lines.

    Just wrap every measure u want to empty when un related table is sliced with this calculation:

    calcMeasureName := ISFILTERED(unRelatedTable[columnName]),Blank(),sum([measure])‏

    • Marked as answer by reapeleg Sunday, September 7, 2014 8:08 AM
    Sunday, September 7, 2014 8:08 AM

All replies

  • hello Rea,

    you indeed have to explicitly filter your measures using the unrelated table.

    i have an 'incident activity' table that holds assignments from one 'group' to another 'group'.  every record holds a 'from group' and 'to group' column.

    then i have an unrelated table 'Incident Assignment Group Parameter' that holds all the 'groups'.  This table is used for a slicer.

    for every 'incident activity' i calculate (using a measure) whether a choosen group from the slicer gets assigned or de-assigned.  from the 'incident activity' table, i filter the 'Incident Assignment Group Parameter' to return a flag that indicates an assignment or de-assignment of one of the choosen groups.

    the measure below is for setting the flag to 1 if there is an assignment:

    calculate
    (
    	if
    	(
    		countrows
    		(
    			filter
    			(
    				'Incident Assignment Group Parameter';
    				'Incident Assignment Group Parameter'[Incident Assignment Group] = values('incident activity'[to group])
    			)
    		)=1;
    		1;
    		blank()
    	)
    )

    Regards,

    Dries

    • Proposed as answer by driezl Tuesday, September 2, 2014 3:06 PM
    Tuesday, September 2, 2014 3:06 PM
  • Thanks

    I was thinking along more simple lines.

    Just wrap every measure u want to empty when un related table is sliced with this calculation:

    calcMeasureName := ISFILTERED(unRelatedTable[columnName]),Blank(),sum([measure])‏

    • Marked as answer by reapeleg Sunday, September 7, 2014 8:08 AM
    Sunday, September 7, 2014 8:08 AM