Forum Discussion

ElvirBotic's avatar
ElvirBotic
Helper III
1 year ago
Solved

Help with Custom Column for filtering

Hello, 

I have a custom column I am working on, that looks up my inspections table and returns if a site has been inspected or not. I have two tables. One is inspections and the other is sites which is where my custom column resides. I want the user to have the ability to filter and see which sites have been inspected for the current quarter, and month. How can I adjust my custom column to achieve this? Current column is as follows...

Was Site Inspected =
IF (
    NOT ISBLANK( 'sites'[site_id] ),
    IF (
        sites[site_id]
        IN SELECTCOLUMNS (
            RELATEDTABLE (
                inspections ),
                inspections[site_id] ),
                "1" ,
                "0"
    )
)
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi ElvirBotic ,

    This is my ‘inspection’ table and ‘sites’ table.

    I add two calculation columns to calculate which sites have been inspected for the current quarter, and month.

    Current Quarter = 
VAR CurrentYear = YEAR(TODAY())
VAR CurrentMonth = MONTH(TODAY())
VAR StartQuarter = DATE(CurrentYear, FLOOR((CurrentMonth -1) / 3 , 1) * 3 + 1, 1)
VAR EndQuarter = EOMONTH(StartQuarter, 2)
RETURN
IF (
    NOT ISBLANK('sites'[site_id]),
    IF (
        'sites'[site_id] IN SELECTCOLUMNS (
            FILTER (
                inspection,
                inspection[inspection_date] >= StartQuarter &&
                inspection[inspection_date] <= EndQuarter
            ),
            "site_id", inspection[site_id]
        ),
        "1",
        "0"
    )
)
    Current Month = 
VAR CurrentYear = YEAR(TODAY())
VAR CurrentMonth = MONTH(TODAY())
VAR StartMonth = DATE(CurrentYear, CurrentMonth, 1)
VAR EndMonth = EOMONTH(StartMonth, 0)
RETURN
IF (
    NOT ISBLANK('sites'[site_id]),
    IF (
        'sites'[site_id] IN SELECTCOLUMNS (
            FILTER (
                inspection,
                inspection[inspection_date] >= StartMonth &&
                inspection[inspection_date] <= EndMonth
            ),
            "site_id", inspection[site_id]
        ),
        "1",
        "0"
    )
)

    We could get the final results below.

    Best regards,

    Lucy Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Gabry's avatar
    Gabry
    Super User
    1 year ago

    Hello,

    Sites table is on the oneside of the relationship right? Because it is the dim table.

    So can't you just do this?

    Check inspected=
    var _rows= countrows(filter(inspections ),inspections[site_id]= EARLIER(sites[site_id]))
    return
    if (_rows>0, "Inspected", "Not inspected")

    Let me know

    • ElvirBotic's avatar
      ElvirBotic
      Helper III
      1 year ago

      I tried this, but I did not get the result I was looking for. I created the column in my inspections list, but I am unsure how to approach this. 

      • Gabry's avatar
        Gabry
        Super User
        1 year ago

        Nope man, this calculated column must be done on sites table

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    ElvirBotic 

    Modified DAX:
    Was Site Inspected =
    IF (
    NOT ISBLANK('sites'[site_id]),
    IF (
    COUNTROWS(
    FILTER(
    'inspections',
    'inspections'[site_id] = 'sites'[site_id] &&
    YEAR('inspections'[inspection_date]) = YEAR(TODAY()) &&
    QUARTER('inspections'[inspection_date]) = QUARTER(TODAY())
    )
    ) > 0,
    "Yes",
    "No"
    ),
    "No"
    )

    Replace the QUARTER logic with MONTH for month-level filtering:

    MONTH('inspections'[inspection_date]) = MONTH(TODAY())

    💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ElvirBotic ,

    This is my ‘inspection’ table and ‘sites’ table.

    I add two calculation columns to calculate which sites have been inspected for the current quarter, and month.

    Current Quarter = 
VAR CurrentYear = YEAR(TODAY())
VAR CurrentMonth = MONTH(TODAY())
VAR StartQuarter = DATE(CurrentYear, FLOOR((CurrentMonth -1) / 3 , 1) * 3 + 1, 1)
VAR EndQuarter = EOMONTH(StartQuarter, 2)
RETURN
IF (
    NOT ISBLANK('sites'[site_id]),
    IF (
        'sites'[site_id] IN SELECTCOLUMNS (
            FILTER (
                inspection,
                inspection[inspection_date] >= StartQuarter &&
                inspection[inspection_date] <= EndQuarter
            ),
            "site_id", inspection[site_id]
        ),
        "1",
        "0"
    )
)
    Current Month = 
VAR CurrentYear = YEAR(TODAY())
VAR CurrentMonth = MONTH(TODAY())
VAR StartMonth = DATE(CurrentYear, CurrentMonth, 1)
VAR EndMonth = EOMONTH(StartMonth, 0)
RETURN
IF (
    NOT ISBLANK('sites'[site_id]),
    IF (
        'sites'[site_id] IN SELECTCOLUMNS (
            FILTER (
                inspection,
                inspection[inspection_date] >= StartMonth &&
                inspection[inspection_date] <= EndMonth
            ),
            "site_id", inspection[site_id]
        ),
        "1",
        "0"
    )
)

    We could get the final results below.

    Best regards,

    Lucy Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.